How to generate CRL installation script

This article descibes solution of common situation, when you have CLR assembly (.NET DLL file), which you need to install into Microsoft SQL Server (CLR is supported from Microsoft 2005). You can use either deploy function of Microsoft Visual Studio or DACI command-line interface of DatAdmin database administration tool.

Step 1 - create assembly

You need compiled DLL assembly file. You can use some tutorial describing this, or help in MSDN. Or you can use our sample project with compiled DLL, which can be downloaded at the and of this article.

Step 2 - create loading SQL script

The loading script should contain:

• dropping objects related to previous load of this assembly
• drop assembly
• create SQL assembly object from DLL file data
• create related objects (stored procedures, functions, aggregates) - neccessary for using objects in SQL

Use command line tool DACI installed with DatAdmin:

daci genasmsql --infile YOUR_DLL_FILE > OUTPUT_SQL_FILE

The simplest SQL file looks like following:

CREATE ASSEMBLY [SqlServerProject1] FROM 0x4D5A90000300...(binary form of assembly) WITH PERMISSION_SET = SAFE
GO
CREATE FUNCTION [StringMultiply] (@s nvarchar(4000), @n int) RETURNS nvarchar(4000) EXTERNAL NAME [SqlServerProject1].[UserDefinedFunctions].[StringMultiply]

Step 3 - load SQL script into database

Run this SQL script (full version generated from your assembly) in your SQL console on target database.

If you use our sample script, you can test result using this SQL command:

select dbo.StringMultiply('test ', 5) -- StringMultiply is function written in C#

Resources

SqlServerProject1.zip - project for MS Visual Studio 2008, and loading script