There are several steps involved.
The 1st part is straight forward enough, the following code gives an example
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
CREATE
PROCEDURE
CLRTestSP
- Create the CLR Dll for the SQL function to use, and copy it to SQL Binn
- Register the CLR Dll in SQL server
- 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.
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
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 nameSql 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', 90For
SQL Server 2008:
EXEC sp_dbcmptlevel 'TestDB', 100“TestDB”
Database Name
