SQL Server CLR store procedure

There are several steps involved.

1. Create the CLR Dll for the SQL function to use, and copy it to SQL Binn
2. Register the CLR Dll in SQL server
3. Create a normal SQL function that uses the CLR Dll
   

The 1st part is straight forward enough, the following code gives an example

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

public partial class StoredProcedures

{

[Microsoft.SqlServer.Server.SqlProcedure]

public static void StoredProcedure1()

{

SqlPipe sp;

sp = SqlContext.Pipe;

String strCurrentTime = "Current System DateTime is: " + System.DateTime.Now.ToString();

sp.Send(strCurrentTime);

}

}

I copied the SqlServerCLRProject.Dll to the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn directory of the SQL server machine.

I also copied the Dll generated (SqlServerCLRProject.Dll) to the C:\ drive on the SQL server machine, as it makes the Dll registration code that needs to be run for SQL a bit easier.
So we’ve copied to \binn and C:\ so far, so now we need to register the Dll with SQL server. So lets look at that

Allow CLR types in the SQL server installation. Which is either done using the following SQL

EXEC dbo.sp_configure ‘clr enabled’,1 RECONFIGURE WITH

Once this is done we can register the CLR Dll with SQL, as follows

create assembly SqlServerCLRProject from ‘c:SqlServerCLRProject.dll’ WITH PERMISSION_SET = SAFE

Now that we've done that, all that left to do is create a normal SQL server store procedure that uses the CLR Dll. Which is simply done as follows

CREATE PROCEDURE CLRTestSP

WITH EXECUTE AS CALLER AS EXTERNAL NAME [SqlServerCLRProject].[StoredProcedures].[StoredProcedure1]

finally execute store procedure

EXEC CLRTestSP  

Register assembly in sql server

1. Copy dll in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn directory

2. create assembly assemblyname from ‘local folder path’ WITH PERMISSION_SET = SAFE

Or

Open sql server → Database → Programmability → Assemblies → New Assemblies → Browse assemblies path → press ok button

3. Enable CLR

4.

CREATE PROCEDURE CLRTestSP

WITH EXECUTE AS CALLER AS EXTERNAL NAME [SqlServerCLRProject].[StoredProcedures].[StoredProcedure1]

“SqlServerCLRProject” is Assembly name.

“ StoredProcedures” class name

“StoredProcedure1” methode name

Sql server Error

SQL SERVER – Fix : Error : Incorrect syntax near . You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the stored procedur “sp_dbcmptlevel”

Change the database compatibility level using following command.

For SQL Server 2005:
EXEC sp_dbcmptlevel 'TestDB', 90

For SQL Server 2008:
EXEC sp_dbcmptlevel 'TestDB', 100

“TestDB” Database Name