Hello World with SQLCLR

Overview

This article will show you how to create a very simple "Hello World" stored procedure using VB.NET or C# to help introduce this feature and demonstrate how to load a .Net assembly into SQL Server 2005.

.NET Code

The first thing we need to do is write our .Net code for our Stored Procedure and compile it. In this case, we are simply going to send the string "Hello World from SQLCLR !!" back to the client. We do this by using the SqlPipe.Send method of the SqlContext object. This can be thought of as the context of the spid currently executing the stored procedure. The Send method of the SqlPipe object can be used to send messages back to the client.

[C#]
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;

public class SQLCLR{

   [Microsoft.SqlServer.Server.SqlProcedure]
   public static void HelloWorld()
   {
      SqlContext.Pipe.Send("Hello World from SQLCLR !!");   
   }
}

In order for us to use this code in SQL Server 2005 we must first compile it into an assembly. We can then load it into SQL Server and create our TSQL Stored Procedure header for our .Net code. The command below demonstrates how to compile the code into an assembly. For the purposes of this article we will assume that the code has been saved in a source file called c:\HelloWorld.vb or c:\HelloWorld.cs. To compile this open a command prompt and execute the relavent lines to change to the .NET 2.0 directory and compile the code.

[C#]
cd "%SystemRoot%\Microsoft.NET\Framework\v2.0.50727"
csc /target:library c:\HelloWorld.cs

TSQL Code

Now that we have created our assembly (c:\HelloWorld.dll) we can load it into SQL Server and create our Stored Procedure. We do this by using the new CREATE ASSEMBLY command as below.We need to ensure that execution of user defined CLR code is enabled using sp_configure

exec sp_configure 'clr enabled', 1
reconfigure
go
create assembly HelloWorld from 'c:\HelloWorld.dll'
with permission_set = safe

Now that we have loaded our assembly, we can create our Stored Procedure. To do this we simply use the standard TSQL CREATE PROCEDURE syntax but instead of defining a TSQL procedure body, we specify an EXTERNAL NAME that points to our .NET Code. We specify the assembly, class and method name as shown below.

create procedure HelloWorld as external name HelloWorld.SQLCLR.HelloWorld

Now that the Stored Procedure is created, we can simply execute it and see the result.

exec HelloWorld

[Results]

Hello World from SQLCLR !!

Accessing External Resources

ALTER DATABASE SCRIBEINTERNAL
SET TRUSTWORTHY ON

CREATE assembly HelloWorld from 'c:\SQLCLR\ClassLibrary1.dll' 
with permission_set = UNSAFE

References

see http://social.msdn.microsoft.com/Forums/en/sqlnetfx/thread/58b01172-1487-46a9-88bc-bd0970782d1d

Creating an Assembly

http://msdn.microsoft.com/en-us/library/ms189524.aspx

Supported Assemblies

http://msdn.microsoft.com/en-us/library/ms403279.aspx