·
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)
