Doug H (DougH@discussions.microsoft.com) writes:
[quoted text, click to view] > I have a CLR stored procedure project containing several stored
> procedures in C#.NET 2005 that is deployed to SQL2005. When I Deploy the
> project from Visual Studio.NET, all the permissions on SQL2005 are
> deleted for all of the stored procedures. I have to reset them every
> time I deploy a change to the code. This is a bit annoying, especially
> if I did only modify one of the stored procedures.
>
> Is this normal behavior, or is there a way to retain permissions once
> they are set? If it is normal behavior, is there a best practices
> document somewhere that recommends how to manage CLR stored procedures?
> Like, is it a good idea to have multiple procs in a project, etc.?
If you ask me, best practice is to use command line and stay away from
Visual Studio. I have not used VS myself for CLR modules in SQL Server,
but from many posts I have seen, I get the impression that it causes a
lot of confusion with little benefit.
When you change your assembly, all you need is to do ALTER ASSEMBLY,
either from file or from hexstring. The only thing which is tricky here,
is when you don't have SQL Server on your own machine. Then you need
to get the assembly to the SQL Server box. Or converth the DLL to a hex
string.
When you do ALTER ASSEMBLY, all that happens is that the assembly is
updated, and the procedure definition and permissions are not touched.
There is one case where this does not work: when you change the interface,
for instance add a parameter. In this case you will need to drop the
procedure definition, and restore permissions.
As for whether to have one or many procedure per assembly, it depends.
I think it is a bad idea to have an assembly MyStoredProcedures with
all sorts of stuff in it. But if you have a couple of procedures that
uses common code, it may be better to have them all in one assembly.
If you have one assembly for each procedure and for the common code,
you get more assemblies to handle, and if you change the interface of
the common code, you need to drop all assemblies, and thus all stored
procedures, even if the interface exposed to SQL is unchanged.
I should add that if you want response based on better understanding
of Visual Studio, a forum devoted to that product may be a better choice.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at
