Creating a CLR Integrated Stored Procedure
While working on the videos for the MCITP SQL Server 2005 Database Administration All-in-One Exam Guide
(Exams 70-431, 70-443, & 70-444) (All-in-One)
book , I came across the idea of creating a CLR assembly from scratch and following
it all the way through to integrating it into a database object such as a stored procedure.
I've found that the beginning part of the process is fuzzy to many non programmers,
so the full picture was never fully grasped. The tests don't require you to
know how to create the assembly, but you should know how to manipulate some of the
properties of the assembly, and we cover that in the video.
In this blog, I just want to lay down the procedures from beginning to end to create
the assembly, add it to your database, create a CLR integrated stored procedure
and execute it.
Create the assembly code
Open an instance of your reliable text editor (Notepad) and enter the following
code into it:
Imports System
Imports System.Data
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlTypes
Public Class CLRClass
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub HappySub()
Dim strWeekDay As String
strWeekDay = WeekdayName(Weekday(Today()))
SqlContext.Pipe.Send _
("Happy " & strWeekDay & "!")
End Sub
End Class
Save this file as as "CLRCode.vb" in a folder such as c:\CLR.
Compile the assembly
Use Windows Explorer
to find the exact version of the Framework on your system. Browse to:
C:\Windows\Microsoft.Net\Framework
Identify the exact version of Framework v2. It is very likely v2.0.50727.
Open a command prompt. Change the directory to the version 2 directory.
cd C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727
Enter the Following command to compile your assembly
vbc /target:library c:\clr\CLRCode.vb
This will create a DLL file named CLRCode.dll in your c:\clr directory. This
is the assembly.
Add the assembly to your database
Open SSMS and create
a new query window.
Create a test database with the following code:
CREATE DATABASE CLRTest
Add the following code to add the assembly to your CLRTest database.
USE CLRTest;
GO
CREATE ASSEMBLY Success
FROM 'c:\CLR\CLRCode.dll'
WITH PERMISSION_SET = SAFE
You can now use the SSMS Object Explorer to browse to the CLRTest | Programmability
| Assemblies container and see your assembly named Success.
Right click the Success assembly and select Properties. Notice the permission
set is currently set to SAFE. This is where you'd change it but it can't be
changed until the database's TRUSTWORTHY property is set to on. You can do
that with the following statement, but this should only be done if the assembly
needs to access files outside of the database.
ALTER DATABASE CLRTest
SET Trustworthy ON
Create the stored procedure
With the CLR assembly in the database, you can now create a CLR integrated stored
procedure and execute it with the following script.
USE CLRTest;
GO
CREATE PROC uspMCITPSuccess
AS
EXTERNAL NAME
Success.CLRClass.HappySub;
GO
Exec uspMCITPSuccess;
Happy days!
|