Deploy and Debug CLR store procedure

Introduction

Hi All,<o:p>

<o:p> 

Here is some cool stuff related to Deploy and Debugging of CLR Store Procedure, I will cover here how to Deploy and Debugging CLR store procedure using VS 2005.<o:p>

I suggest before reading this article please read my previous articles named “CLR store procedure vs. T-SQL store procedure” and “How to create CLR store procedure”. <o:p>

1.      While deploying CLR store procedure you must have “CREATE ASSEMBLY” permission. Once deployed on SQL Server you can see and execute CLR store procedure there and then only as sown below.

2.      You might observe “Test Script” folder while creating CLR store procedure in VS 2005. This folder contains “Test.sql” file, if you open and see that file it consist different section each for specific task like execution of store procedure, user defined function etc. This script file is purely made for debugging purpose. Please follow give steps to debug CLR store procedure.  <o:p>

Steps for debugging CLR store procedure,<o:p>

a.      Open “Test.sql” file and insert following code at the end of file. <o:p>

EXEC dbo.GetRoles <o:p>

--Created in previous article named “How to create CLR store procedure”  <o:p>

EXEC dbo.PrintDate 'This will go into txt file'<o:p>

b.      “PrintDate” store procedure access external resources like file system. This store procedure is writing to a file. While accessing external resources, need “TRUSTWORTHY” permission.<o:p>

c.      Fire following command to achieve “TRUSTWORTHY” permission.<o:p>

ALTER DATABASE Database_Name SET TRUSTWORTHY ON  <o:p>

d.      What follows is code for “PrintDate” CLR store procedure.<o:p>

 [Microsoft.SqlServer.Server.SqlProcedure]<o:p>

       public static void PrintDate(string name)<o:p>

        {<o:p>

            <o:p>

            SqlPipe p;<o:p>

            p = SqlContext.Pipe;<o:p>

FileIOPermission filePerm = new FileIOPermission(FileIOPermissionAccess.AllAccess, @"C:\TestFile.txt");<o:p>

            filePerm.Assert();<o:p>

            StreamWriter sw = new StreamWriter(@"C:\TestFile.txt");<o:p>

            sw.WriteLine(name);<o:p>

            sw.Flush();<o:p>

            sw.Close(); <o:p>

            p.Send(System.DateTime.Today.ToString());<o:p>

using (SqlConnection connection = new <o:p>

<o:p> 

SqlConnection("context connection=true"))<o:p>

            {<o:p>

            connection.Open();<o:p>

SqlCommand command = new SqlCommand("SELECT * FROM     Department", connection);<o:p>

            SqlDataReader reader = command.ExecuteReader();<o:p>

                  reader.Read();<o:p>

                  SqlContext.Pipe.Send(reader);<o:p>

            }<o:p>

<o:p> 

     }<o:p>

e.      Since “PrintDate” CLR store procedure access external resources as discussed above, we need to incorporate this change in VS 2005 before deploying CLR store procedure as shown below. Following screen shows setting of “Permission Level” as “External”.  <o:p>

<o:p>

<o:p> 

f.       Now I assume you have successfully deployed CLR store procedure and incorporated respective execute command in “Test.sql”.<o:p>

g.      Mark break point in “PrintDate” function and Run the application in VS 2005. Application halt at break point like any .NET application. <o:p>

    <o:p>

h.      After execution complete you can observe “TestFile.txt” get generated with text inside passed as input parameter to CLR store procedure, check out “Output” window which contains results for “GetRole” CLR store procedure and SQL statement of “PrintDate” function "SELECT * FROM     Department" as shown below.<o:p>

<o:p>

<o:p> 

3.      To enable CLR on SQL Server please fire following query, on particular database.<o:p>

EXEC sp_configure 'show advanced options' , '1';<o:p>

go<o:p>

reconfigure;<o:p>

go<o:p>

EXEC sp_configure 'clr enabled' , '1'<o:p>

go<o:p>

reconfigure;<o:p>

-- Turn advanced options back off<o:p>

EXEC sp_configure 'show advanced options' , '1';<o:p>

go <o:p>

<o:p>