.Net Fun - Mike's Personal C#, .Net, SQL Server, and Oracle Development and DBA Site

Tested with .Net 2.0.40607.42 and SQL Server 2005 Express 9.00.852

One of the new, exciting features of SQL Server 2005 is .Net CLR Integration. This tutorial will show you how to create CLR Stored Procedures using the C# compiler (csc.exe) and SQL Server 2005. We'll be creating a simple CLR Stored Procedure that prints a message to the executing caller (context).

  1. Enabling CLR Integration in SQL Server 2005

    Before continuing, make sure that CLR Integration is enabled on the SQL server. To do this, simply execute the system stored procedure sp_configure on the SQL Server with advanced options enabled: EXEC sp_configure @configname = 'Show Advanced Options', @configvalue = 1 RECONFIGURE WITH OVERRIDE GO EXEC sp_configure Look for a record where the name is clr enabled and check its run_value - it should be 1. If it's not, you'll need to set it to 1 in order to to enable CLR Integration in SQL Server 2005. To do this, execute the following T-SQL code: EXEC sp_configure @configname = 'clr enabled', @configvalue = 1 RECONFIGURE WITH OVERRIDE GO There are three ways you can execute the above code against the SQL Server:
    1. SQL Server 2000 Query Analyzer
    2. SQL Server 2005 SQLCMD Console Utility
    3. SQL Server 2005 Management Studio

  2. Create a CRL Stored Procedure Assembly

    1. Create a class file and call it DotNetFun.SQLServer.SProc.cs.
    2. Copy and paste the following C# 2.0 into the DotNetFun.SQLServer.SProc.cs file and save it. using System; using System.Data.Sql; using System.Data.SqlServer; using System.Data.SqlTypes; namespace DotNetFun.SqlServer { public static class SProc { public static int PrintMessage(String Message) { int i = 0; try { SqlContext.GetPipe().Send(Message); } catch (Exception err) { i = 1; SqlContext.GetPipe().Send("An error occured: " + err.Message); } return i; } } }
    3. Compile the DotNetFun.SQLServer.SProc.cs class file using the following command:
      csc /target:library DotNetFun.SQLServer.SProc.cs /reference:"C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlaccess.dll"

      Note: The path to sqlaccess.dll may vary according to where your MS SQL Server 2005 application files are installed. Choose the appropriate path. CLR Integration in SQL Server 2005 is possible via sqlaccess.dll. The command assumes you're running csc from the same directory as DotNetFun.SQLServer.SProc.cs, otherwise you should type in the full path to DotNetFun.SQLServer.SProc.cs. Compiling the class should produce an assembly file called DotNetFun.SQLServer.SProc.dll.

  3. Add the CLR Stored Procedure Assembly to SQL Server 2005

    Execute the following T-SQL code against the SQL Server: CREATE ASSEMBLY DotNetFunSQLServerSProc FROM 'C:\Inetpub\wwwroot\dotnetfun\code\2.0\DotNetFun\SQLServer\SProc\DotNetFun.SQLServer.SProc.dll' WITH PERMISSION_SET = SAFE GO Note: Use the actual path to the DotNetFun.SQLServer.SProc.dll assembly. The path above is where mine was stored when I added it to SQL Server.

    There are three ways you can execute the above code against the SQL Server:
    1. SQL Server 2000 Query Analyzer
    2. SQL Server 2005 SQLCMD Console Utility
    3. SQL Server 2005 Management Studio

  4. Create a SQL Server 2005 Stored Procedure that Consumes the CLR Stored Procedure

    Execute the following T-SQL code against the SQL Server: CREATE PROC sp_DotNetFunSProcPrint @Message NVARCHAR(255) AS EXTERNAL NAME DotNetFunSQLServerSProc.[DotNetFun.SqlServer.SProc].PrintMessage GO Note how the full namespaced path to the class, SProc, is fully qualified. This is important as SQL Server will expect it to be there. If you don't, you'll get an error saying the class could not be found. Also, We're surrounding our fully qualified path in brackets because SQL Server doesn't allow you to use dots (.) as they are typically interpreted as being path identifiers for SQL Server objects.

    CLR Stored Procedure Execution Syntax: EXTERNAL NAME [SQL Server Assembly Name].[Fully Qualified Path to Class].[Static Method Name] If you create a simply class file with a single class that isn't encapsulated inside of one or more namespaces, then you won't have to use brackets to qualify the path to the class. Rather, you could qualify the path to the class by just supplying the name of the class that encapsulates the static method: EXTERNAL NAME DotNetFunSQLServerSProc.SProc.PrintMessage

  5. Execute the SQL Server 2005 Stored Procedure

    Now you're ready to execute the SQL Server stored procedure that'll end up executing the CLR Stored Procedure, DotNetFunSQLServerSProc.[DotNetFun.SqlServer.SProc].PrintMessage. Execute the following T-SQL code against SQL Server: EXEC sp_DotNetFunSProcPrint "Hello from DotNetFun.com!" The execution should result in the message, "Hello from DotNetFun.com!", being printed. You can also vary the message supplied to the stored procedure through a stored procedure variable: DECLARE @Message NVARCHAR(255) SET @Message = 'Hello there!' EXEC sp_DotNetFunSProcPrint @Message Notice how we used the NVARCHAR SQL Server data type - this is necessary as it's the only variable character type in SQL Server that's compatible with the .Net String type. Using VARCHAR will produce an error.