Click here to Skip to main content
Click here to Skip to main content

Deploy and Debug CLR store procedure

, 15 Oct 2006
Rate this:
Please Sign up or sign in to vote.
This article will drive you, how deploy and debug CLR store procedure
<!-- Article Starts - DO NOT ADD HTML/BODY START TAGS-->

Introduction

Hi All,

 

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.

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”.

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.

Sample screenshot

 

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. 

Steps for debugging CLR store procedure,

a.      Open “Test.sql” file and insert following code at the end of file.

EXEC dbo.GetRoles

--Created in previous article named “How to create CLR store procedure” 

EXEC dbo.PrintDate 'This will go into txt file'

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.

c.      Fire following command to achieve “TRUSTWORTHY” permission.

ALTER DATABASE Database_Name SET TRUSTWORTHY ON 

d.      What follows is code for “PrintDate” CLR store procedure.

 [Microsoft.SqlServer.Server.SqlProcedure]

       public static void PrintDate(string name)

        {

           

            SqlPipe p;

            p = SqlContext.Pipe;

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

            filePerm.Assert();

            StreamWriter sw = new StreamWriter(@"C:\TestFile.txt");

            sw.WriteLine(name);

            sw.Flush();

            sw.Close();

            p.Send(System.DateTime.Today.ToString());

using (SqlConnection connection = new

 

SqlConnection("context connection=true"))

            {

            connection.Open();

SqlCommand command = new SqlCommand("SELECT * FROM     Department", connection);

            SqlDataReader reader = command.ExecuteReader();

                  reader.Read();

                  SqlContext.Pipe.Send(reader);

            }

 

     }

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”. 

Sample screenshot 

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

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

Sample screenshot    

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.

Sample screenshot 

3.      To enable CLR on SQL Server please fire following query, on particular database.

EXEC sp_configure 'show advanced options' , '1';

go

reconfigure;

go

EXEC sp_configure 'clr enabled' , '1'

go

reconfigure;

-- Turn advanced options back off

EXEC sp_configure 'show advanced options' , '1';

go

 

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Malpani Amol
Web Developer
India India
I am a .NET developer cum designer. I work for Zensar Technologies Ltd. Pune (INDIA), I consult, I develop, I debug and besides that I speak at conferences and user groups and I also write. I have written some articles on WCF, WF, SSIS, Compression decompression, CLR Store Procedure, SQL Server Reporting Services 2005, Extended grid and lot more to count.
Reach to me on:-a.malpani@zensar.com

Comments and Discussions

 
GeneralDeploy and Debug CLR User Defined Functions Pinmemberfranklinraj12-Nov-09 7:06 
GeneralThanks Pinmembersashidhar30-Oct-09 19:22 
GeneralExcellent article PinmemberKaila.Kamlesh9-Sep-07 21:58 
GeneralThanx for sharing Pinmembersalman_mubbashir8-Dec-06 21:48 
GeneralNon-VS deployment PinmemberMark Nischalke15-Oct-06 5:31 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.141015.1 | Last Updated 15 Oct 2006
Article Copyright 2006 by Malpani Amol
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid