SQL CLR INTEGRATION

The common language runtime (CLR) integration feature is off by default in Microsoft SQL Server, and must be enabled in order to use objects that are implemented using CLR integration. To enable CLR integration, use the clr enabled option of the sp_configure stored procedure as shown:

sp_configure 'clr enabled', 1

GO

RECONFIGURE

GO

You can disable CLR integration by setting the clr enabled option to 0. When you disable CLR integration, SQL Server stops executing all CLR routines and unloads all application domains.

Computers configured with large amounts of memory and a large number of processors may fail to load the CLR integration feature of SQL Server when starting the server. To address this issue, start the server by using the -g memory_to_reserve SQL Server service startup option, and specify a memory value large enough.

This topic provides an overview of the namespaces and libraries required to compile database objects using the Microsoft SQL Server integration with the Microsoft .NET Framework common language runtime (CLR). The topic also shows you how to write, compile, and run a simple CLR stored procedure written in Microsoft Visual C#.

Required Namespaces

The components required to develop basic CLR database objects are installed with SQL Server 2005. CLR integration functionality is exposed in an assembly called system.data.dll, which is part of the .NET Framework. This assembly can be found in the Global Assembly Cache (GAC) as well as in the .NET Framework directory. A reference to this assembly is typically added automatically by both command line tools and Microsoft Visual Studio, so there is no need to add it manually.

The system.data.dll assembly contains the following namespaces, which are required for compiling CLR database objects:

System.Data

System.Data.Sql

Microsoft.SqlServer.Server

System.Data.SqlTypes

Writing A Simple "Hello World" Stored Procedure

To Create a new VS 2005 CLR project,

1. Open VS 2005 IDE.

2. Click on the Create Project.

3. Choose the language you want.

4. Expand the Language you’ve chosen and choose Database.

5. Select the SQL Server Project and click OK.

Copy and paste the following Visual C# code into a text editor, and save it

C#

using System;

using System.Data;

using Microsoft.SqlServer.Server;

using System.Data.SqlTypes;

public class HelloWorldProc

{

[Microsoft.SqlServer.Server.SqlProcedure]

public static void HelloWorld()

{

SqlContext.Pipe.Send("Hello world!\n");

}

}

Visual Basic

Imports System

Imports System.Data

Imports Microsoft.SqlServer.Server

Imports System.Data.SqlTypes

Public Class HelloWorldProc

<Microsoft.SqlServer.Server.SqlProcedure> _

Public Shared Sub HelloWorld()

SqlContext.Pipe.Send("Hello world!\n")

End Sub

End Class

This simple program contains a single static method on a public class. This method uses two new classes, SqlContext and SqlPipe, for creating managed database objects to output a simple text message. This method can be declared as a stored procedure in SQL Server, and then run in the same manner as a Transact-SQL stored procedure.

DEPLOYING the PROJECT from Visual Studio 2005

1. Right on the project and choose DEPLOY.
2. If you get an ERROR like this,

Error 1 Incorrect syntax near 'EXTERNAL'. 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 procedure sp_dbcmptlevel.HelloWorldCLR

Then run this procedure in the SQL Server against the Database to which we’re deploying the Assembly.

sp_dbcmptlevel 'pubs'

If the result returned is less that 90, then run the command,

sp_dbcmptlevel 'pubs', 90

1. Now RE-DEPLOY the assembly.
2. To view / modify the connection settings of the database, Go to the Project menu of the Visual studio, Click on the Project Properties and choose Database tab. You can find the connection settings there.
3. After deploying it, Go to the SQL Server, Expand the Database to which you’ve deployed, Expand Programmability, Expand Stored Procedure and you’ll be able to see the Procedure HelloWorld .
4. Now go to the Assembly and Expand it. You’ll see the Assembly that you’ve created. (The Project name).

DEPLOYING the PROJECT if you’re using the Command line Utility

Compiling the "Hello World" Stored Procedure

SQL Server installs the .NET Framework redistribution files by default. These files include csc.exe and vbc.exe, the command-line compilers for Visual C# and Visual Basic programs. To compile our sample, modify your path variable to point to the directory containing csc.exe or vbc.exe. The following is the default installation path of the .NET Framework.

C:\Windows\Microsoft.NET\Framework\(version)

Version contains the version number of the installed .NET Framework redistributable. For example:

C:\Windows\Microsoft.NET\Framework\v2.0.31113

Once you have added the .NET Framework directory to your path, you can compile the sample stored procedure into an assembly with the following command.

For Visual C# source files:

csc /target:library helloworld.cs

For Visual Basic source files:

vbc /target:library helloworld.vb

The /target option allows you to compile it into an assembly.

These commands launch the Visual C# or Visual Basic compiler using the /target option to specify building a library DLL.

Loading and Running the "Hello World" Stored Procedure in SQL Server

Once the sample procedure has successfully compiled, you can test it in SQL Server. To do this, open SQL Server Management Studio and create a new query, connecting to a suitable test database (for example, the AdventureWorks sample database).

We will need to create the assembly so we can access the stored procedure. For this example, we will assume that you have created the helloworld.dll assembly in the C:\ directory. Add the following Transact-SQL statement to your query.

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

Operations that you need to carry out in SQL Server 2005

Once the assembly has been created, we can now access our HelloWorld method by using the create procedure statement. We will call our stored procedure "hello":

CREATE PROCEDURE hello

AS

EXTERNAL NAME helloworld.HelloWorldProc.HelloWorld

//AssemblyName.ClassName.ProcedureName

Once the procedure has been created, it can be run just like a normal stored procedure written in Transact-SQL. Execute the following command:

EXEC hello

This should result in the following output in the SQL Server Management Studio messages window.

Hello world!

Removing the "Hello World" Stored Procedure Sample

When you are finished running the sample stored procedure, you can remove the procedure and the assembly from your test database.

First, remove the procedure using the drop procedure command.

drop procedure hello

Once the procedure has been dropped, you can remove the assembly containing your sample code.

drop assembly helloworld

Here is the complete code for your Reference.

--To enable SQL Server as CLR enabled – Only first time

sp_configure 'clr enabled', 1

GO

RECONFIGURE

GO

--Make sure the DB compatible level is 90, which refers to SQL Server 2005.

sp_dbcmptlevel 'pubs'

if O/P is <= 80 then,

sp_dbcmptlevel 'pubs', 90

60 = SQL Server 6.0

65 = SQL Server 6.5

70 = SQL Server 7.0

80 = SQL Server 2000

90 = SQL Server 2005

//Visual Studio code for Reference.

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 HelloWorld()

{

SqlContext.Pipe.Send("Hello world!\n");

}

};

-- Create a New Procedure that will refer the CLR Procedure.

use pubs

go

CREATE PROCEDURE hello

AS

EXTERNAL NAME HelloWorldCLR.StoredProcedures.HelloWorld

-- Execute the CLR Procedure using the newly created reference procedure.

EXEC hello