Access .Net functions in SQL Server 2005 Using CLR

According to Microsoft, SQL Server 2005 significantly enhances the database programming model by hosting the Microsoft .NET Framework 2.0 Common Language Runtime (CLR). This enables developers to write procedures, triggers, and functions in any of the CLR languages, particularly Microsoft Visual C# .NET, Microsoft Visual Basic .NET, and Microsoft Visual C++.

How do we implement this functionality?
In order to use CLR we have to:

1) Write a .NET class with a public function
2) Compile the class to a DLL
3) Register the DLL with SQL Server
4) Create a SQL Server function to access the .NET function

Creating an example function and DLL
To provide a simple example that you can use, we need to first create a new project. In Visual Studio, start a new Class Library project and call it SQLServerCLRTest. Then, create a new Class called CLRFunctions and add the following code:

Public Class CLRFunctions
Public Shared Function HelloWorld(ByVal Name As String) As String
Return (“Hello ” & Name)
End Function
End Class

You’ll notice that we have a simple function (which is Public Shared so that SQL Server can access it) which accepts a name as parameter and returns a simple message saying “Hello” to whichever name is passed in.

Now, we need to create a DLL out of this project so that we can register it with SQL Server. The easiest way to do this, is to simply click the Build->Build Solution menu item in VS which will build the application and create the DLL. As your project will be in debug mode the DLL will usually be found at a location such as:

C:Documents and Settingsmark.smithMy DocumentsVisual Studio

Once you’ve located this DLL we can copy it over to our SQL Server machine or simply make a note of this path if that happens to be the same machine as our development machine.

Turn on CLR functionality
By default, CLR is disabled in SQL Server so to turn it on we need to run this command against our database:

exec sp_configure ‘clr enabled’,1

Registering the DLL
In order to use the function we wrote, we first have to register the DLL with SQL Server. To do this we have to create an assembly, assign it a name and point the assembly at the DLL. Using the path to the DLL we created, run the following command against the database:

CREATE ASSEMBLY asmHelloWorld FROM ‘C:SQLServerCLRTest.dll’

Accessing our function from SQL Server
In order to access our .NET function, we need to create a SQL Server function which makes use of the “EXTERNAL NAME” option which informs SQL Server that we will be using a CLR function. The function we will be creating will look like this:

CREATE FUNCTION dbo.clrHelloWorld
@name as nvarchar(200)
RETURNS nvarchar(200)
AS EXTERNAL NAME asmHelloWorld.[SQLServerCLRTest.CLRFunctions].HelloWorld

There’s two things to note about the above function. The first is that we use an nvarchar which will be the equivilant of the .NET string (it will produce an error if you try to use a varchar and the second is the format of the “EXTERNAL NAME” path. From the reading I’ve done, the format should be:


However, when I tried this I received an error from SQL Server stating that it couldn’t find the type, so in order to get it to work I had to use the format:


Calling the function
Now that we’ve registered our function, we should be able to call it by using the following statement:

SELECT dbo.clrHelloWorld(‘Shail’)

Hopefully, when you run this code you’ll get a simple “Hello Shail” returned in your results.

This is obviously just an example to show you how to implement CLR, but to create something useful you could look at creating functions that deal with Regular Expressions, interact with the File System or Registry, send emails or simply access any resources that are located outside of SQL Server.

Posted in Microsoft Technology Tagged with: