Click here to Skip to main content
13,256,482 members (52,215 online)
Click here to Skip to main content
Add your own
alternative version


95 bookmarked
Posted 9 Aug 2007

Execute .NET Code under SQL Server 2005

, 26 Aug 2007
Rate this:
Please Sign up or sign in to vote.
Article describes all the problems and constraints defined to use managed code under SQL Server 2005.


First of all, I would like to congratulate the Microsoft Development Team for their superb and innovative technology using which we can use managed code written in any of the .NET supported languages in Microsoft SQL Server 2005 Stored Procedures. If I make it simple, then "Now you can call any function written in .NET class library in your SQL Server stored procedure". This technology will allow us to use the features of .NET language with the extendibility of SQL Server.


I got an article of using MSMQ from SQL Server in CodeProject written by a gentlemen regarding MSMQ, and Messaging was used from SQL SP. I tried to show the constraints and limitations of using such assemblies and the required configuration in SQL Server 2005, so that the developers can begin. And I am sharing the practical problems which I faced in the actual implementation of this.

Before starting, you have to enable the managed code execution feature of the SQL Server which is disabled by default. To enable it, execute the following code as a query in your SQL Server query editor.

sp_configure 'clr enable', 1

Now your server is ready to run managed code under its runtime. But let me tell you it's not magic but it's the same technology being used by the .NET framework. The only important part is that now SQL Server is also able to execute code on CLR. Isn't it great? Before writing this article, I did a lot of research on this. I got several articles explaining the same. But I faced a lot of problems because things were scattered. Now I am trying to simulate the whole procedure in this article. I did this study because of my specific requirements. I was using Message Queuing Service of Microsoft in C# .NET. But after some time, I got a requirement in which I needed to send a message from a Stored Procedure to MSMQ Queue. And till that time for me it was possible by using the System. Messaging namespace of .NET runtime only. I know most of the developers are wondering about MSMQ. Don't worry, I won't go into details of that right now. We will start with a simple application which will use a simple string returning function of C# .NET class library. Let's start with the class library.

  • Start .NET 2005 Studio.
  • Open a new class library.
  • Select C# as the language.
  • Name the project as ManagedCodeAndSQLServer.
  • By default, you will find a class named Class1.cs created for you.
  • Rename it as BaseFunctionClass.cs.

Create a simple function in the class as follows:

using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

namespace ManagedCodeAndSQLServer
    public class BaseFunctionClass
        #region "Default Constructor"
        public BaseFunctionClass()

        #region "Welcome Function"
        /// <summary>
        /// This function will be called from the SQL Stored Procedure.
        /// </summary>
        /// <param name=""strName"">Name</param>
        /// <returns>Welcome Message</returns>
        public static void GetMessage(SqlString strName, out SqlString  
          strMessge = "Welcome," + strName + ", " + "your code is getting
          executed under CLR !";


Important Points

I hope you are able to notice some new things in the above code. First of all, the [SqlProcedure] attribute on the GetMessage function intimates the CLR that the function is callable from SQL Server Stored procedure. The things that I am telling you are my own experiences; you may not find these things in articles published on the Internet. When using a CLR function, you must remember that the SQL server nvarchar is equivalent to System.String. But the most important part is that "CLR enabled procedure can only return Int32, Int and void data types". That's why I have used output parameter with the GetMessage function. Also I have used SqlString instead of System.String. Please keep all the above things in mind.

Now build the project and leave it because your work from the .NET class library point of view is over. Now come to your SQL Server again. I hope you still remember that we enabled CLR integration in our server at the start of the article. If not, then don't wait for me to come and enable it.

We have to register ManagedCodeAndSQLServer.dll as an assembly in our database. To register the assembly, you should have owner rights in your database or you should be the local system admin or Server Admin. The assemblies that we are going to register should be registered in UnSafe mode. Otherwise they would not be able to access resources external to SQL Server 2005. And to register an unsafe assembly, you should have 'unsafe' rights enabled in your username or role. All the above things are very important so take care of them, otherwise you won't be able to register your assembly. So create a database 'TestingCLR'. Before registering the .NET assembly in the database, you have to set the trustworthy option of the database on using the following code:


Now register ManagedCodeAndSQLServer.dll under that using the following code:

FROM 'E:\Important\SQL\ManagedCodeAndSQLServer\ManagedCodeAndSQLServer\bin\

I am again saying if the dbo is not having 'unsafe assembly' right, it won't work. Similarly if I am using a specific class of .NET in my DLL, I have to register it prior to DLL registration in the same way. For example, I am using System.Messaging in my assembly, then I have to register it using the following code:

FROM 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Messaging.dll'

To use the normal functionality, we need not register the DLL from .NET runtime as in our case. Now in your server explorer, go to your TestingCLR database, select Programmability and in Assemblies, you will find an assembly registered with the name given by you, i.e., "HelloDotNet". Now you are almost done. You should know the full path (namespace convention) of the function that you are going to use from that assembly. In our case, it is: ManagedCodeAndSQLServer.BaseFunctionClass and GetMessage is our function to be used. Now I am creating a simple Stored Procedure to use this assembly as follows:

CREATE PROCEDURE usp_UseHelloDotNetAssembly
@name nvarchar(200),
@msg nvarchar(MAX)OUTPUT
AS EXTERNAL NAME ManagedCodeAndSQLServer.[ManagedCodeAndSQLServer.

To execute the procedure:

DECLARE @msg varchar(MAX)
EXEC usp_UseHelloDotNetAssembly 'Kittu And Tannu',@msg output
PRINT @msg

You will get the following output: Welcome, Kittu And Tannu, your code is getting executed under CLR !

Try it and enjoy!


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Kamal Singh Kharayat
Technical Lead Government of Dubai
United Arab Emirates United Arab Emirates
Thank you for visiting my profile at Code Project.

I have a proven track record of delivering excellence in working on many technically challenging project across the organizations I have had worked with. My core competencies are SharePoint Development, Administration, ASP.NET, .NET MVC, SQL Server.

Adding great values to the project and team is what I work for. I believe we should deliver the best possible solutions to the customer without compromising best practices and recommendations. Also for a better project management we need to juggle things like time, cost, risk, and quality and customer satisfaction in a successful project delivery.

Specialties: Competent technologist in the field of Microsoft .NET and SharePoint Technologies.
“A successful application development always leads to Feasibility, Reliability, Scalability and more importantly a better usability.”

Blogspot Articles: Visit my blog

"Improvement is the only thing which makes me happy...."
e-mail Addresses:
skype id: stek_ks

You may also be interested in...


Comments and Discussions

GeneralEnable CLR Pin
kurisutofuJP26-Aug-07 16:27
memberkurisutofuJP26-Aug-07 16:27 
QuestionError in naming? Pin
kurisutofuJP26-Aug-07 16:22
memberkurisutofuJP26-Aug-07 16:22 
AnswerRe: Error in naming? Pin
Kamal Singh D. Kharayat26-Aug-07 19:24
memberKamal Singh D. Kharayat26-Aug-07 19:24 
GeneralRe: Error in naming? Pin
kurisutofuJP26-Aug-07 20:03
memberkurisutofuJP26-Aug-07 20:03 
GeneralThanx!! Had not known this functionality of SQL server... Pin
nishantsagar8317-Aug-07 23:32
membernishantsagar8317-Aug-07 23:32 
QuestionInteresting article but a few questions Pin
Jon Masters15-Aug-07 0:08
memberJon Masters15-Aug-07 0:08 
AnswerRe: Interesting article but a few questions Pin
Kamal Singh D. Kharayat15-Aug-07 21:29
memberKamal Singh D. Kharayat15-Aug-07 21:29 
GeneralRe: Interesting article but a few questions Pin
Jon Masters16-Aug-07 0:01
memberJon Masters16-Aug-07 0:01 
Haha! No not on opposite sides of the LOC although as it happens, I did once live in Murree in Pakistan but I didn't join in Smile | :) . We're just two engineers trying to do the best by our customers, I'll try to use more smileys in future Smile | :)

Also, I realise that the whole System.Messaging thing was not the main point of your excellent and very useful article, and you just mentioned it in passing. I also realise that you're not passing judgement on whether or not it's a good idea, just explaining how to do it Smile | :) . The only reason I brought it up was because it's something I recently tried and I've not managed to convince myself that it's a good solution Smile | :) . I do like the article, honest!

Wrt the System.Messaging dll, I just did it again, importing it using management studio and I get the message 'Create Failed for SqlAssembly System.Messaging... Warning: The .Net frameworks assembly 'system.messaging' are registering is not fully tested in the SQL hosted environment.' and management studio won't load it. If you use a query instead, you're right it does load the assembly and its dependencies, but obviously they all produce the same warning. Also, I notice that System.Messaging dependencies include System.Windows.Forms. Presumably there's a dialog class somewhere in System.Messaging that needs it but I'm not sure I want that in my database too.

So let's say we use it anyway and we run into a problem and I call MS support and tell them what I'm doing. The risk I take is that I'm told that the assembly hasn't been tested in that environment, I was told that when I loaded it into SQL Server and I'm on my own Sigh | :sigh: . Now I'm sure MS tech support is more helpful than that but I need to convince my management too Smile | :)

Regarding the threads, processes, state etc when I thought about it initially I thought the same as you (and I have to say that I'm not a database expert by any stretch of the imagination so I'm working mainly on assumption here) however, this is all fine if your solution must scale up but what effect is scaling out going to have? Also, when I say that my objects are at the mercy of Sql Server, what I mean is, at what stage will my assembly be loaded into memory and how long is it going to stay there? Which thread will be executing my code (I assume it could be more than one)? I would suggest that the minute you find yourself having to ask questions like that, the code needs to come out of the database because it's probably in the wrong place.

BTW, am I right in thinking that the assembly is actually copied into the database? In which case, how do you handle software upgrades? In order to update the CLR code you'd have to drop the assembly and re-create it but that means also dropping anything in the database that is dependent on it e.g. stored procedures, triggers etc and re-creating those too.

Now I know that in an ideal world all of our systems are really well designed and silly and obvious things like that are picked up during the design process but as I'm sure we both know, the reality tends to be somewhat different Smile | :)

I think that all I'm really trying to say to anyone interested in doing something like this is that:

1. IMO this article is great and it will tell you everything you need to know;
2. Having the ability to run CLR code from Sql Server is great but...
3. As with all these things, understand the limitations and use it with caution. Smile | :)

Jon Masters
Lead Software Engineer
GeneralRe: Interesting article but a few questions Pin
Kamal Singh D. Kharayat16-Aug-07 0:43
memberKamal Singh D. Kharayat16-Aug-07 0:43 
GeneralRe: Interesting article but a few questions Pin
Jon Masters16-Aug-07 1:08
memberJon Masters16-Aug-07 1:08 
GeneralExcellent!!!!!!!!!!!!!!! Pin
Rashmi Patel RJ14-Aug-07 4:06
memberRashmi Patel RJ14-Aug-07 4:06 
GeneralVery Help Full Pin
sneharahul14-Aug-07 2:55
membersneharahul14-Aug-07 2:55 
GeneralInteresting Pin
lallous13-Aug-07 22:37
memberlallous13-Aug-07 22:37 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.171114.1 | Last Updated 27 Aug 2007
Article Copyright 2007 by Kamal Singh Kharayat
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid