Mohamed Zaatar's Blog

CLR integration is a .NET facility allows you to write .NET code and be called and executed from SQL Servers. Simply it is a CLR hosting in Microsoft SQL Server beginning from SQL Server 2005. You can write SP, user define function, user define Data Types, user-define aggregate in .NET.

How to step into CLR integration:

1- Enable CLR integration in your SQL Server, by run this command:

sp_configure 'clr enabled', 1

GO

RECONFIGURE

GO

2- Write the mentioned code in your IDE:
C# Code:

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 zaatar()
{
// Put your code here
using (SqlConnection sc =
new SqlConnection("Server=SERVERNAME\INSTANCENAME;Database=DB_Name;Trusted_Connection=True;"))
{
using (SqlCommand cmdupdate = new SqlCommand("update Country set Active = 1"))
{
cmdupdate.Connection = sc;
sc.Open();
cmdupdate.ExecuteNonQuery();
}
sc.Close();
}
}
};

3- Save the file as MyFirstProc.cs in c:\

4- compile it using .NET Frame work 2.0 /3.0 / 3.5 ,(but i preferred 2.0), using this command:

C:\Windows\Microsoft.NET\Framework\v2.0.50727>csc /target:library /output:c:\zaatar.dll c:\zaatar.cs

hints:
-i used CMD under administrator authority
- /output option: to specify the output location.
- /target option: to specify the output type

5- Go to your SQL Server and type this command to create an Assembly inside your DB :

CREATE ASSEMBLY ASS_Update
from 'c:\zaatar.dll' WITH PERMISSION_SET = SAFE
GO


6- After successfully creation the assembly, create a stored procedure to deal with the assembly and just to call the dll, by typing :

CREATE PROCEDURE dbo.UpdateCountryTable
AS
EXTERNAL NAME zaatar.StoredProcedures.zaatar
GO

Hints: After EXTERNAL NANME, there are three names with dot (.) separated
the first : is the dll name.
the second : is the class name in the CS file
and the third is the method defined in the CS file

7- Now you can call the Stored Procedure normally like any SP in the SQL Server.

Problems Hints:

-1- If you receive this error: “create assembly for assembly XYZ failed because assembly XYZ failed verification”

Solution: Try to compile your file or project with Framework 2/3/3.5,if you are working in VS IDE, change the target framework from project prosperities.

-2- If you receive this error: “The server principal "XYZ" is not able to access the database "XYZ" under the current security context”

Solution: Run this command on your DB:

ALTER DATABASE DB_NAME SET TRUSTWORTHY ON

GO

-3- if you are trying to set mode of your Assembly to UNRISTRICTED and you receive this error: “The database owner SID recorded in the master database differs from the database owner SID recorded in database DB_NAME.”

Solution: Run this command on your DB:

--To get owner SID recorded in the master database for the current database

SELECT owner_sid FROM sys.databases WHERE database_id=DB_ID()

--To get the owner SID recorded for the current database owner

SELECT sid FROM sys.database_principals WHERE name=N'dbo'

Each of the SID should be equal, if not run this command:

ALTER AUTHORIZATION ON Database::DB_NAME TO sa