CLR Integration in SQL Server 2005
page 1 of 7
Published: 15 Dec 2006
In this article Soyuj discusses the intergration of Common Language Runtime (CLR) in .NET applications using SQL Server 2005.
by Soyuj Kumar Sahoo
Average Rating: 
Views (Total / Last 10 Days): 44413/ 72


We all know how great T-SQL is for database code, but writing procedural code in T-SQL has always been difficult.  It seems impossible when we need a stored procedure or two, requiring some array-operations, text-parsing or complex math operations.  However, it is quite easy if our Server allows us to deploy C# or VB.NET code that is used within the Server process. In other words, if we need complex procedural code, we can write it as managed code.

With the Common Language Runtime (CLR) hosted in Microsoft SQL Server 2005(called CLR integration), we can write stored procedures, triggers, user- defined functions, user-defined types, and user-defined aggregates in managed code.  Because managed code compiles to native code prior to execution, we can achieve significant performance increases in some scenarios.  Thus SQL Server essentially acts as the operating system for the CLR when it is hosted inside SQL Server.

Let us go through an example for details understanding.  The most important factor is that we should have SQL Server 2005 with .NET Framework version 2.0 installed in our Server with Visual Studio 2005.

View Entire Article

User Comments

Title: CLR integration with SQL2005   
Name: Narender
Date: 2009-09-29 7:38:59 AM
Very informative and nice example...
Title: Can we do with CSC C#   
Name: Kaushal Yadav
Date: 2009-05-26 8:29:43 AM
can we do it with csc compiler?
while i m tring with csc, dll is not generating,
is there any other way to gnerate dll with csc compiler/
guide me.

Thanks in advance.
Title: Great Article (No way to go any where)   
Name: Kaushal Yadav
Date: 2009-05-26 8:26:19 AM
This is very helpful for learning prospective.
a lot of thanks for this article
Title: Very very excellent Article   
Name: Prashant Bhande
Date: 2009-04-27 6:17:30 AM
This is the article i am finding from last few days..
Its very helpful for me in my project.

Thanks Dear .........Soyuj Kumar Sahoo
Title: Excellent Article   
Name: Matt Hedenberg
Date: 2009-04-22 12:00:02 PM
Thanks for the tutorial and samples, just excellent!
Title: getting syntax error while compling it on dos promt   
Name: Kaushal
Date: 2009-04-11 9:40:02 AM
public class clsmyclr
public clsmyclr()

public static void myclrpro(int intCatID)
using (SqlConnection sqlCon = new SqlConnection("context connection=true"))
SqlCommand cmdsel = new SqlCommand("Select * from tblcategory where catid=" + intCatID, sqlCon);
SqlDataReader oReader = default(SqlDataReader);
oReader = cmdsel.ExecuteReader();
catch (Exception ex)

hie Soyuj , its really gd article but i m getting syntax error while creating assembly, so may i knw where i m going wrong
Title: Thanks!   
Name: Eric Fickes
Date: 2009-03-21 5:03:51 PM
Thanks, this post works like a champ! Bummer it's in VB though ;)
Title: Very good and helpfull article.   
Name: Rajesh Verma
Date: 2009-01-22 6:21:20 AM
This is what i m searching for a long, really very helpful.
thaks Soyuj
Title: best one   
Name: niraj
Date: 2008-09-10 4:02:12 PM
thank you for this article
Title: Good one   
Name: Vijay Kumar
Date: 2008-02-20 1:13:32 AM
Hi Soyuj,
Really a nice article.

Thank you
Vijay Kumar
Software Engineer
Title: CLR Integration   
Name: Hemant Sudehely
Date: 2008-02-11 2:53:52 AM
Thanks for providing such a good article to understand features of sql-server2005. Its really good to read.
Title: Very nice Article   
Name: Pankaj Bahuguna
Date: 2008-01-14 6:17:46 AM
Thanks Soyuj Kumar Sahoo,

Just because of this code,
I have been able to implement CLR Integration in my application
Title: Nice Article   
Name: Sumit Kumar Sharma
Date: 2007-09-27 7:30:01 AM
Hi Soyuj,
This article is really useful for me to builed and deploy the Assemblies into Sql Server.

Thanks Once again....!!!
Title: Execution of the CLR Stored Procedure failed   
Name: Raaz
Date: 2007-08-20 9:06:57 AM
The following are the steps that were followed to call the webservice from CLR Stored Procedure...

I was able to create the assembly and CLR stored procedure in SQL SERVER but failed at final execution of the procedure....

wsdl /o:TerraService.cs /n:ClrWebServices.Test
cLR Procedure

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using ClrWebServices.Test;

public partial class StoredProcedures
public static void InsertLocation(SqlString city,
SqlString state, SqlString country)
using (SqlConnection cn =
new SqlConnection("context connection=true"))
string query = "INSERT INTO dbo.Locations
(City,State,Country,AerialMap) VALUES
(@City, @State, @Country, @Image)";

using (SqlCommand insertCommand =
new SqlCommand(query,cn))
SqlParameter[] sqlParams =
new SqlParameter[4];
sqlParams[0] =
new SqlParameter("@City", city);
sqlParams[1] =
new SqlParameter("@State", state);
sqlParams[2] =
new SqlParameter("@Country", country);
sqlParams[3] =
new SqlParameter("@Image",



public static byte[] GetLocationImage(SqlString city,
SqlString state, SqlString countr
Title: Can't deploy the assembly   
Name: Mr SQL
Date: 2007-03-30 7:23:15 AM
I get an error when attempting to deploy the assembly to SQL Server Express. It comes up with

Msg 6544, Level 16, State 1, Line 1
CREATE ASSEMBLY for assembly 'MyCLRAssembly' failed because assembly 'MyCLRAssembly' is malformed or not a pure .NET assembly.
Unverifiable PE Header/native stub.

Does anybody know hot to fix this? I have followed all the steps as above and they worked fine until I hit this error.

Many thanks.
Title: WonderFul One   
Name: Vinuraj
Date: 2007-03-15 6:10:12 AM
Its a Wonderful One I am searching. With a Brief Descrition I Got it.
Title: Its realy nice and very very important stuff for creating the system.web.dll   
Name: Nitin Shende
Date: 2007-02-06 6:26:08 AM
Thanx for the detail description.
This article covers all the steps required for the creating the assembly, which reference of the system.web.dll
Do posting more and more.
Title: Problem with system.web.dll   
Name: Josh
Date: 2007-01-15 5:09:17 PM
I'm having the same problem as Lan calling CREATE ASSEMBLY on an assembly which references System.Web.dll.

See this post for more info. Please respond with help there.


Title: Need help for deploying assembly   
Name: Lan
Date: 2007-01-14 9:04:23 AM
This is great! Thank you for the detail description.
I'm trying to deploy a simple assembly that references the System.Web.HttpUtility class. I got the following error:

Warning: The Microsoft .Net frameworks assembly 'system.web, version=, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=x86.' you are registering is not fully tested in SQL Server hosted environment.
Warning: The Microsoft .Net frameworks assembly 'system.drawing, version=, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment.
Msg 6218, Level 16, State 2, Line 1
CREATE ASSEMBLY for assembly 'System.Web' failed because assembly 'System.Drawing' failed verification. Check if the referenced assemblies are up-to-date and trusted (for external_access or unsafe) to execute in the database. CLR Verifier error messages if any will follow this message
[ : System.Drawing.BufferedGraphicsContext::bFillColorTable][mdToken=0x6000243][offset 0x00000053][found address of Byte] Expected numeric type on the stack....

When I tried to deploy the system.web.dll, I got the same error. Any idea of what could be the problem?

Thank you!
Title: Its really cool   
Name: vas
Date: 2007-01-08 10:00:25 AM
Its one of the best article i read so far. keep it up. do posting more...
Title: fed back for soyuj   
Name: ranjan patra
Date: 2006-12-26 9:57:51 AM
the way u discussed all the things is awosome.
it is always expected from a guy like u to share u'r knowledge with others.
as i personally know u it is a great pleasure to get some basic idea about stored procedures .
keep it up.
tk cr
Title: Very Very Important and new stuff   
Name: Abhishek Kumar Singh
Date: 2006-12-20 5:25:37 AM
This article covers one of the newest and important functionality of SQL Server 2005. As a .net programmer I got to know feature to implement. Thanx Soyuj for providing such a wonderful content.

Community Advice: ASP | SQL | XML | Regular Expressions | Windows

©Copyright 1998-2024  |  Page Processed at 2024-06-17 6:19:31 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search