Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Compiling and Deploying a CLR Assembly

Compiling and Deploying a CLR Assembly 

.NET Framework 2.0

This topic provides an overview of the namespaces and libraries required to compile database objects using the Microsoft SQL Server integration with the Microsoft .NET Framework common language runtime (CLR). The topic also shows you how to write, compile, and run a simple CLR stored procedure written in Microsoft Visual C#.

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:





Writing A Simple "Hello World" Stored Procedure

Copy and paste the following Visual C# code into a text editor, and save it in a file named "helloworld.cs" or "helloworld.vb".

using System;
using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;

public class HelloWorldProc
    public static void HelloWorld()
        SqlContext.Pipe.Send("Hello world!\n");

This simple program contains a single static method on a public 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 then run in the same manner as a Transact-SQL stored procedure.

We will now compile this program as a library, load it into SQL Server, and run it as a stored procedure.

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.


Version contains the version number of the installed .NET Framework redistributable. For example:


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 Running the "Hello World" Stored Procedure in SQL Server

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

We will need to create the assembly so we can access the stored procedure. For this example, we will assume that you have created the helloworld.dll assembly in the C:\ directory. Add the following Transact-SQL statement to your query.

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

Once the assembly has been created, we can now access our HelloWorld method by using the create procedure statement. We will call our stored procedure "hello":

EXTERNAL NAME helloworld.HelloWorldProc.HelloWorld

Once the procedure has been created, it can be run just like a normal stored procedure written in Transact-SQL. Execute the following command:

EXEC hello

This should result in the following output in the SQL Server Management Studio messages window.

Hello world!

Removing the "Hello World" Stored Procedure Sample

When you are finished running 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

Community Additions

© 2015 Microsoft