I have yet to find an article or other resource with practical information on deploying an assembly into a SQL Server database, and I will kill anyone that tells me to invoke the Deploy command in Visual Studio.
Where do I put it before pulling the assembly into the database?
How do I prepare the database to accept a CLR assembly?
Do I use a normal setup routine to an install folder and then invoke my deployment scripts using the code-base in that folder?
Going Backward Slowly
Hi Brady, how about invoking the Deploy command in Visual Studio?
:) But this is probably more the info you're looking for...
Enabling CLR Integration - http://msdn2.microsoft.com/en-us/library/ms254506(VS.80).aspx
And then the T-SQL for importing - CREATE ASSEMBLY See- http://msdn2.microsoft.com/en-us/library/ms189524.aspx
If you search for MSBUILD and SQLCLR you should also get some tasks you can use in your automated builds to deploy your assemblies.
armanddp:Hi Brady, how about invoking the Deploy command in Visual Studio?
I know where you work
armanddp::) But this is probably more the info you're looking for... Enabling CLR Integration - http://msdn2.microsoft.com/en-us/library/ms254506(VS.80).aspx And then the T-SQL for importing - CREATE ASSEMBLY See- http://msdn2.microsoft.com/en-us/library/ms189524.aspx If you search for MSBUILD and SQLCLR you should also get some tasks you can use in your automated builds to deploy your assemblies.
Thanks Armand, but those are already old news to me. I was working on a copy of our server database, and the server uses SQL authentication. So when I started working on a restored backup on my machine, I switched to SQL auth. because the database doesn't have a user for my Windows login. Then, to create the UNSAFE assembly it told me the database owner needs the UNSAFE ASSEMBLY permission. That's where I 'hakked vas'. I had to go back and log in through Windows auth. because then I could grant a proper user, not the 'dbo' alias, the required permission.
ProfK: but those are already old news to me.
I thought as much, unfortunately I don't do much SQL work so can't really be of more help on this. Just wanted to pull your leg about the killing part :) Hopefully our office move happens sooner rather than later :P
OK, sorry for the late reply, time etc.
ProfK:Where do I put it before pulling the assembly into the database?
Anywhere you want. The CREATE ASSEMBLY statement allows you to specify the location where your assembly is stored.
ProfK:How do I prepare the database to accept a CLR assembly?
The database must have the "clr enabled" option set. You can set this by executing
sp_configure ‘clr enabled’, 1 GO RECONFIGURE GO
ProfK:Do I use a normal setup routine to an install folder and then invoke my deployment scripts using the code-base in that folder?
That would work, yes. I've never done a CLR proc using a deployment package before. I've always just created the assembly and registered it using the appropriate T-SQL statements. Your way is probably better.