CLR integration

The common language runtime (CLR) integration feature is turned off by default in Microsoft SQL Server. It should be enabled in order to use objects that are implemented for CLR integration. To enable CLR integration, use the “clr enabled” option of the sp_configure stored procedure as shown in the below listing.

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 of SQL Server’s service startup option, and specify a memory value large enough.

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 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 can be executed in the same way as a Transact-SQL stored procedure.

DEPLOYING the PROJECT from Visual Studio 2005

1.      Right click 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 executing the "Hello World" Stored Procedure in SQL Server 2005

Once the sample procedure has successfully compiled, you can test it against the SQL Server 2005. 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).

For this example, we will assume that you have created the helloworld.dll assembly in the C:\ directory. Add the following Transact-SQL statement in your query.

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

Execution in SQL Server 2005

Once the assembly has been created, we can now access our HelloWorld method by using the create procedure statement.

CREATE PROCEDURE hello

AS

EXTERNAL NAME helloworld.HelloWorldProc.HelloWorld

//AssemblyName.ClassName.ProcedureName

Once the procedure has been created, it can be executed just like a normal stored procedure written in Transact-SQL.

EXEC hello

The following message will be outputted in SQL Server Management Studio messages window.

Hello world!

Removing the "Hello World" Stored Procedure Sample

When you are finished with the execution of 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