CLR procedure deployment without Visual Studio 2005

As a user of Visual Studio Standard 2005, I miss some of the nice stuff like integration with SQL Server when it comes to deploying CLR objects.

I recently, had a fairly sophisticated query to write, and I was trying to avoid multiple trips to and from the web server and the database server, for performance reasons. The original code was calling multiple procedures from a fairly involved loop in code. Despite every attempt to rationalise and tune it, it was "slow man". After some investigation I decided that the multiple trips between servers must be removed.

With out going into the ins and outs of how to write CLR procedures, this quick article shows you how to deploy them locally and to remote servers without the need for a Visual Studio 2005 that supports CLRSQL deployment.

Firstly write and build your DLL (i.e. a class library). Mine is called ClrProc.dll for this example. It contains a class (type) called Procs and a single method called DoIt().

For ease of this demo I have copied my dll to the root of C. That is C:\ClrProc.dll.

Open SQL Server Management Studio and open a new query window targeting the appropriate database.

Enter the following to register the assembly with SQL Server 2005.

CREATE ASSEMBLY MyClrProcAssembly from 'c:\ClrProc.dll' WITH PERMISSION_SET = SAFE

This has registered the assembly and if you drill down into the Assemblies branch of that database you should see it (you may need to refresh).

Now we need to register the method within the assembly as a procedure. Type the following:

CREATE PROCEDURE procDoIt
AS
EXTERNAL NAME MyClrProcAssembly.[MyClrProcAssembly.Procs].DoIt

Note that in the above the  format of the 3rd line is assembly.type.method. However, sometimes it is required to include the fully referenced Assembly.Type for the "Type" part of this line. As I haven’t been able to work out why it’s sometimes needed I have got into the habit of doing the fully qualified version. So that actual format of that 3rd line is Assembly.[Assembly.Type].Method

Ok, that’s done. Set your permissions accordingly and you’re good to go.

Now to deploy this to a production server.

The easiest way is to right click the assembly and create a "create script" which can then be executed on the production machine. Note you don’t need access to the remote SQL Server’s file system so this is a neat way of doing it on a shared hosting scenario.

Then go ahead and script your procedures to create them on the target machine as well.

Finally note, that CLR functionality is turned off by default so this must be enabled. Ask your host of they support them first. Secondly, you must have CREATE ASSEMBLY permissions on the servers involved.

Advertisements

2 thoughts on “CLR procedure deployment without Visual Studio 2005

  1. Hi i did all the steps to deploy my dll, but it send me that error CREATE ASSEMBLY failed because it could not open the physical file “D:\MyCLRSQL.dll”: 21(error not found).

    So i run this test -> exec master.dbo.xp_fileexist ‘…Microsoft SQL Server\80\Tools\binn\MyCLRSQL.dll’ and result that the file not exist, if a run that but for a this file ‘…Microsoft SQL Server\80\Tools\binn\atl80.dll’ that dll exists.

    Thank for advance, tell me what i can do it,? need deploy my clr.

  2. @ Grass

    You need to use the exact path to your dll in the CREATE ASSEMBLY statement. So in your case include the full path .. something like
    ‘d:\Program Files\Microsoft SQL Server\90\Tools\Binn\MyCLRSQL.dll’

    From the looks of things you have placed your dll there but when you tried to create the assembly you have used d:\MyCLRSQL.dll’

    Hope thats the problem and this helps.

    Cheers

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s