SQL CLR Integration in SQL Server 2008

Introduction

Google is a great tool for finding information on the Internet. For developers Google (or Bing) are great tools for finding blogs that describe how to employ technical solutions. The biggest challenge is finding the correct and a complete solution. It is pretty straight forward to find partial solutions; it is much harder to find complete solutions.

Part of the difficulty in finding a complete solution is that there are so many environments, configurations, and configuration settings one small variable can make your environment different and the solution (found on the Internet) not quite right for your environment. Perhaps an answer is to try to clearly articulate the environment(s) the solution was tested in and document as complete a solution as possible. Since a lot of blogs are unpaid volunteer efforts, there is no way to control the quality of posts. As a result sometimes you have to put a solution together from several posts.

In this column I try to incorporate as many details as possible, describe some scenarios, and talk about the environment the solution was tested in. (Even I can’t test all environments and configurations.)

This article talks about SQL/CLR (Common Language Runtime, or .NET Framework) Integration. That is, calling .NET CLR code from SQL. The solution was implemented in Microsoft Visual Studio 2010, SQL Server Express 2008, and uses .NET Framework 3.5. The integration code is trivial, but the solution shows you how to configure the database server and define wrapper stored procedures and functions for the CLR methods. In the name of completeness, the end of the article talks about attaching to the SQL Server instance and debugging the .NET framework CLR code.

After reading this article you will know how to enable the CLR in SQL Server, check the SQL Server compatibility level, create a key file for signing a CLR Class Library, defining methods that can be called from SQL Server, registering the assembly in SQL Server, defining procedure and function wrappers for the CLR code, and attaching the Microsoft Visual Studio debugger to the SQL Server instance and stepping into your CLR code.

Checking the SQL Server Compatibility Level

So this article doesn’t turn into a book I am going to keep the short stuff short, or at least relatively short.

SQL Server and CLR integration was introduced for SQL Server 2005 and 2008. It is a relatively new capability in the grand scheme of things so check your compatibility level. You can do this in Microsoft Visual Studio or Microsoft SQL Server Management Studio by creating a new query and running.

  exec sp_dbcmptlevel <database_name>

For example, the sample database for this article is ClrIntegration so the command to check the current compatibility level would be:

  exec sp_dbcmptlevel ClrIntegration

To set the compatibility level to SQL Server 2005 revise the command to:

  exec sp_dbcmptlevel ClrInteration, 90

The next step is to enable CLR integration.

Enabling CLR Integration in SQL Server (2008)

SQL is a bunch of tables and stored procedures that contain housekeeping behaviors and schemas. Add a new database to a server instance or new table to an existing database and the pre-installed stored procedures and tables somewhere are updated to contain the definition of these new elements.

One such stored procedure is sp_configure. One of the settings of sp_configure is ‘clr enabled’ which is 0 by default. To enable CLR integration type in a query window in Microsoft Visual Studio or SQL Server Management Studio.

  exec sp_configure  'clr enabled', 1
  RECONFIGURE;
  GO

The RECONFIGURE statement updates currently configured values of a configuration option and performs a SQL stop and restart if necessary.

Creating a Key File for the CLR Class Library

You can create a key file-a .snk file-by opening the Microsoft Visual Studio command prompt and running the sn.exe utility with the -k option. I thought this file might be needed for CLR assemblies with the PERMISSION_SET = UNSAFE option, but it doesn’t appear if you need a key file.

If you determine that you need a key file for your project then select Start|Programs|Microsoft Visual Studio (2010)|Visual Studio Tools|Visual Studio Command Prompt (2010) and enter sn -k <keyfile.snk> name at the command prompt, copy the .snk file to your project.

  To associate the strong key file with your project select Project|<project name> Properties, click the Signing tab and use the drop down "Choose a strong name key file" to associate your key file with the project.

Keep in mind that precise instructions like menu options may vary slightly between different versions of Windows and Microsoft Visual Studio.

Create the Class Library in Microsoft Visual Studio 2010

If is worth noting that if you use the New Project|Database|SQL Server|Visual Basic SQL CLR Database Project option to create your CLR integration project then a key file is not created for you and the Deploy option associated with projects of that type will register and create the assembly associations automatically. The material in this section walks you through using a Class Library project and all of the steps for CLR integration; use the SQL Database Project type if you are starting from scratch.

To manage SQL/CLR integration with regular class library a VB CLR SQL Integration project follow these steps:

  1. Assuming you have a class library project handy or have created one, add a class to it using a meaningful name.
  2. Make sure that the target framework is 3.5 by selecting Project|Properties|Compile (tab)|Advanced Compile Options|Target framework (all configurations) .NET Framework 3.5–see the note.
  3. VB doesn’t have a namespace around classes, but if you do happen to have one then remove it. SQL CLR Integration doesn’t manage namespaces well (for some reason that escapes me), but if you want to use the fully qualified namespaces and class names then see the example in the next section
  4. Add an Imports System.Runtime.InteropServices and Microsoft.SqlServer.Server statements to the top of the class file. Add a shared subroutine with the <SqlProcedureAttribute()> on the shared method and the OutAttribute() on the second parameter; here is the subroutine definition:

      <sqlprocedure()>
      Public Shared Sub GetUserName(ByVal dummy As Integer, <out()> ByVal name As String)
      SqlContext.Pipe.Send("In CLR Code")
         name = "Paul Kimmel"
         SqlContext.Pipe.Send("Exiting CLR Code")
    
      End Sub
    

  5. Compile the class library

Note: If you are using Microsoft Visual Studio 2010 as I am for this demo then the default target framework is .NET framework 4.0. Unfortunately, SQL Server doesn’t seem to want to register .NET frameowrk 4.0 assemblies. This is problematic as the framework will age. Ether I am missing something about using .NET 4.0 for CLR to SQL Integration or support going forward may not continue. Until there is a definitive answer future support is a consideration when using this technology.

The entire listing (so far) is provided in Listing 1. SqlProcedureAttribute is used by convention. You use the method from SQL without it, but it is a convenient tag and let’s you provide a SQL-like name if you are into that sort of thing.

The Out() attribute marks the second parameter as an output only parameter. If you want a “stored procedure” then use subroutines with out parameters and define SQL Server stored procedures in SQL; if you want a function then use a Function in VB and a SQL Server Function. Here is Listing 1.

  A VB class that is designed to be called from SQL Server using CLR integration.

  Imports System.Data.SqlClient
  Imports System.Runtime.InteropServices
  Imports Microsoft.SqlServer.Server

  Public Class CallFromSql

      <SqlProcedure()>
      Public Shared Sub GetUserName(ByVal dummy As Integer, <Out()> ByRef name As String)

          SqlContext.Pipe.Send("In CLR Code")
          name = "Paul kimmel"
          SqlContext.Pipe.Send("Exiting CLR Code")

      End Sub

  End Class

Listing 1

More by Author

Must Read