CLR Integration in SQL Server 2005
page 2 of 7
by Soyuj Kumar Sahoo
Feedback
Average Rating: 
Views (Total / Last 10 Days): 54718/ 164

Steps for building a CLR stored procedure in SQL Server 2005

·         Enabling CLR integration in SQL Server 2005

·         Creating a CLR stored procedure Assembly

·         Deploying the Assembly in SQL Server 2005

·         Creating & executing the CLR stored procedure in SQL Server 2005

Enabling CLR integration in SQL Server 2005

Generally, the Common Language Runtime (CLR) integration feature is off by default in Microsoft SQL Server and must be enabled in order to use objects that are implemented using CLR integration.  To enable CLR integration we use the clr enabled option of the sp_configure stored procedure and set the value as "1" as shown below.

Execute the following code against SQL Server with choosing the proper database in SQL Server 2005 Management Studio.

Listing 1

EXEC sp_configure @configname = 'clr enabled', @configvalue = 1
RECONFIGURE WITH OVERRIDE
GO

Creating a CLR stored procedure Assembly

1.    Open the text editor then copy and paste the following code in that.  Save it as a Class file to MyCLRAssembly.vb.

Listing 2

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlTypes
 
Public Class MyFirstCLRProcs
    <Microsoft.SqlServer.Server.SqlProcedure()> _
    Public Shared Sub CLRProcs(ByVal iActionID As Integer)
        
        SqlContext.Pipe.Send("Hello world! It's now " & System.DateTime.Now.ToString())
        Using oConnection As New SqlConnection("context connection=true")
            Try
                oConnection.Open()
       Dim oCommand As New SqlCommand( _ 
"select top 10 EventID ,StatusKey, ProcessTime, UserType, ActionID" & _ 
 " from  EventTable where ActionID = " & _
iActionID, oConnection)
                Dim oReader As SqlDataReader
                oReader = oCommand.ExecuteReader()
                SqlContext.Pipe.Send(oReader)
            Catch ex As Exception
                SqlContext.Pipe.Send(ex.ToString())
            End Try
        End Using
    End Sub
End Class                                                                     
 

2.    Required Namespaces and Classes:

·         Here Microsoft.SqlServer.Server is the main namespace responsible for creating CLR integrated stored procedures, functions, triggers, etc.

·         SqlContext and SqlPipe are two classes responsible for creating objects to output the results and text messages.  Here in the result part we send the SqlDataReader to clientside and print a message as “HelloWorld !” with the Server time.

·         Here we use Context Connection, which allows us to execute Transact-SQL statements in the same context that our code was invoked in the first place, i.e. connect using a connection string that points to local server.

3.    Compile the class file  "MyCLRAssembly.vb" using the following command in SDK command prompt:

·         Go to : start>>All programs>>Microsoft .NET Framework SDK v2.o>>SDK Command Prompt

·         Write the following codes there and press enter.

Listing 3

vbc  /t:library  c:\SQLAssembly\MyCLRAssembly.vb

Here “t” stands for target and “c:\SQLAssembly\” is the physical path of that Class file.  Then we get the MyCLRAssembly.dll assembly file at that folder.

Deploying the Assembly in SQL Server 2005

Execute the following T-SQL code against the SQL Server in Query analyzer of SQL Server 2005 Management Studio.

Listing 4

CREATE ASSEMBLY MyCLRAssembly from ‘c:\SQLAssembly\MyCLRAssembly.dll'  
WITH PERMISSION_SET = SAFE 
Go

Here we have to give the exact path of our assembly file (.dll file) for creating assembly.  Again the important factor here is the PERMISSION_SET for accessing security permissions.  There are three modes: SAFE, UNSAFE, EXTERNAL_ACCESS; through which SQL Server allows users to deploy code in database.  SAFE mode is for all users and UNSAFE is for more reliable user like Database Administrator.

So every user can create Assemblies using SAFE mode. However, there are some restrictions and limited number of Namespaces available to use under this mode.

Creating & executing the CLR stored procedure in SQL Server 2005

·         Creating CLR stored procedure:

Execute the following code in query analyzer of SQL Server 2005 Management Studio against the database.

Listing 5

CREATE PROC sp_MyCLRProcs(@iActionID as int)
AS
EXTERNAL NAME MyCLRAssembly.MyFirstCLRProcs.CLRProcs
Go

Here we have to give [Procedure Name] and must specify the exact path of assembly method as [Assembly Name].[Class Name].[CLR function Name].  As our CLR method, which having the prefix as <Microsoft.SqlServer.Server.SqlProcedure()>, has an input parameter so we have to define the parameter at creation of the stored procedure with the identical datatypes exact variable name. (Here it is "Integer" which is equivalent ot ' int'  in T-SQL)

·         Executing the CLR stored procedure:

As any other stored procedure the CLR stored procedure is executed using EXEC  T-SQL command as follows :

Listing 6

EXEC sp_MyCLRProcs 216268    

The following result/message we get from our stored procedure for the particular ActionID (216268) in the SQL Server 2005 Management Studio:


Figure 1: (O/P Result & Message)


View Entire Article

User Comments

Title: CLR integration with SQL2005   
Name: Narender
Date: 2009-09-29 7:38:59 AM
Comment:
Very informative and nice example...
Title: Can we do with CSC C#   
Name: Kaushal Yadav
Date: 2009-05-26 8:29:43 AM
Comment:
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
Comment:
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
Comment:
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
Comment:
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
Comment:
public class clsmyclr
{
public clsmyclr()
{

}
[Microsoft.SqlServer.Server.SqlProcedure()]
public static void myclrpro(int intCatID)
{
using (SqlConnection sqlCon = new SqlConnection("context connection=true"))
{
try
{
sqlCon.Open();
SqlCommand cmdsel = new SqlCommand("Select * from tblcategory where catid=" + intCatID, sqlCon);
SqlDataReader oReader = default(SqlDataReader);
oReader = cmdsel.ExecuteReader();
SqlContext.Pipe.Send(oReader);
}
catch (Exception ex)
{
SqlContext.Pipe.Send(ex.ToString());
}
}
}
}

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
Comment:
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
Comment:
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
Comment:
thank you for this article
Title: Good one   
Name: Vijay Kumar
Date: 2008-02-20 1:13:32 AM
Comment:
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
Comment:
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
Comment:
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
Comment:
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
Comment:
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 http://www.terraserver-usa.com/TerraService2.asmx
--------------------
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
{
[Microsoft.SqlServer.Server.SqlProcedure]
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",
GetLocationImage(city,state,country));

insertCommand.Parameters.AddRange(sqlParams);

cn.Open();
insertCommand.ExecuteNonQuery();
cn.Close();
}
}
}

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
Comment:
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
Comment:
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
Comment:
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
Comment:
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.
http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/8782336f27e9271c/?lnk=igtc#

Thanks,

Josh
Title: Need help for deploying assembly   
Name: Lan
Date: 2007-01-14 9:04:23 AM
Comment:
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=2.0.0.0, 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=2.0.0.0, 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!
Lan
Title: Its really cool   
Name: vas
Date: 2007-01-08 10:00:25 AM
Comment:
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
Comment:
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
ranjan
Title: Very Very Important and new stuff   
Name: Abhishek Kumar Singh
Date: 2006-12-20 5:25:37 AM
Comment:
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-2019 ASPAlliance.com  |  Page Processed at 2019-04-24 6:33:42 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search