Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Creating Dot Net Stored Procedures in SQL Server 2005

By Yelena Varshal,

by Yelena Varshal
Date 06/03/2005

INTRODUCTION

This article is for those who would like, with minimal coding and with basic tools like Notepad and Query Analyzer and SQL Server 2005 Management Studio, to create and run a custom stored procedure written in .NET, as widely advertised by the SQL Server team for SQL Server 2005.

As an example we will create custom file access and Event Log access stored procedures. Everyone knows that accessing files and logs from T-SQL can be a task. Not every SQL Server DBA is a front-end programmer and has Visual Studio.NET on his or her server. That is why I will show you how to use Notepad with Command-Line compiler to create and compile VB.NET code. I assume that .NET Framework is installed on your computer or you are running Windows XP or Windows 2003 Server or you are on the computer with a SQL Server 2005 CTP. If not, then you may want to download .NET framework from the Microsoft website.

Code Comes First Anyway

First of all we will need to create and compile VB.NET procedures (subroutines) that will let us log a record to a text file and in the Windows Application Log. One subroutine will take a message string as a parameter and log a record to the text file taking a file path and name and a message string as parameters. The other one will log to the Windows Event Log.

First, create a new text file in Notepad called LoggingSP.vb Then copy the code from the bottom of this article that is below the title "CODE FOR LoggingSP.vb" This code contains only declarations and one class YVlog that contains 3 procedures:

LogToTextFile - that opens a specified text log file for append and calls procedure LogIt to log a line
LogIt - that logs a line
LogToEventLog - that logs an event to Windows Application Log as a Source "My Application"

Save this file in your Test folder (directory) on your machine.

Compiler Comes Next

We now have to compile this code using the Visual Basic Command Line Compiler. You will need to run just 2 lines there after creating SetPath batch file. Actually I myself create a second batch file for the complier line, so the whole process takes just 2 clicks. Open a new text file in Notepad, call it SetPath.bat and save in the same Test folder that you saved the file above. Add these 3 lines of code to this file:

@SET FrameworkDir=C:\WINDOWS\Microsoft.NET\Framework
@SET FrameworkVersion=v2.0.50215
@set PATH=%FrameworkSDKDir%\bin;%FrameworkDir%\%FrameworkVersion%;%PATH%;

Save and close this SetPath.bat You have to do it only once. Now, pay attention if you have installed SQL server 2005 or Visual Studio 2005 as it added a newer version of the .NET Framework, v2.0.50215

You may need to modify the path in the first line for the FrameworkDir variable to reflect your Windows or WINNT directories. You may also need to modify the version number in the second line to match the real version number of your .NET Framework. The number should match the Framework version's folder name under C:\WINDOWS\Microsoft.NET\Framework.

Open a Command Prompt. Change the current folder to your Test folder like

cd Test

Now we are ready to set environmental variables and path for Visual Basic complier vbc. To do this, just run SetPath.bat from Command Prompt.

SetPath.bat

We will compile our code from this same instance of the Command Prompt Window. Enter on the command line the following:

vbc /out:LoggingSP.dll /t:library 
    /r:mscorlib.dll,system.dll,microsoft.visualbasic.dll LoggingSP.vb

It should be all in one line. This line essentially says that Visual Basic Compiler should create a dll file from LoggingSP.vb using the resources dlls that are after the /r: switch.

Creating the SQL Server Assembly

The next step is to create an assembly in the SQL Server database that is based on the .dll file that we just complied. To do this, we use a new statement, the CREATE ASSEMBLY command, in SQL Server. We need to run this before the CREATE PROCEDURE statement. My new assembly will be named asbl_LoggingSP.

First you need to copy the LoggingSP.dll to the Tools\Binn folder of your SQL Server 2005 installation for consistent location names. To create the assembly, open the SQL Server 2005 Management Studio and connect to your server. Then click New Query - Database Engine Query on the toolbar

When I did this, I was in Master database on my test server. From there, you should run the following lines:


CREATE ASSEMBLY asbl_LoggingSP 
FROM 'C:\Program Files\Microsoft SQL Server\90\Tools\Binn\LoggingSP.dll'
WITH PERMISSION_SET = UNSAFE

I used the Permission_SET = UNSAFE because the other 2 settings SAFE and EXTERNAL_ACCESS did not let me do what I wanted in the file system and code. See CREATE ASSEMBLY explanations in 2005 BOL.

Creating the Stored Procedures For the Text and Event Logs

Finally! After we have created the assembly, we just have to create stored procedures with CREATE PROCEDURE statement that will use the assembly. We will create 2 stored procedures, each one will be based on the same assembly and same dll. To do this, we run the following in the query window:

CREATE PROCEDURE dbo.vsp_LogToTextFile(@LogName nvarchar(255),@NewMessage 
nvarchar(255))
AS EXTERNAL NAME asbl_LoggingSP.YVlog.LogToTextFile

CREATE PROCEDURE dbo.vsp_LogToEventLog(@errorMessages  nvarchar(255))
AS EXTERNAL NAME asbl_LoggingSP.YVlog.LogToEventLog

Note the naming convention after the EXTERNAL NAME keywords: first the assembly name, then the class name, and then the sub name from the code file. You may have already noticed that both the VB procedures and the SQL stored procedures have input parameters. The vsp_LogToTextFile has 2 parameters, the log file path with name and a message to log and vsp_LogToEventLog accepts a message as an input.

Running the Procedures

Now let's execute our stored procedures. Execute the following:
Execute vsp_LogToTextFile 'C:\z_TestLog21.txt', 'QA Message4'
It will put the following into C:\z_TestLog21.txt file:
Log Entry : 2:32:33 PM Friday, June 03, 2005
  :
  :QA Message4
-------------------------------

To execute the second procedure:

Execute vsp_LogToEventLog 'This is a test message'

It will put 'This is a test message' into Windows Application Log with the source "My Application".

Summary

It took us 4 steps to create a couple of .NET stored procedures
  1. Create one Code File with one class and two subs in Notepad
  2. Set the .NET variables and compile a dll library file in the Command Prompt
  3. Import the Assembly into SQL Server database by running CREATE ASSEMBLY statement
  4. Create two stored procedures using CREATE PROCEDURE statements
You can change the log source to better suit your needs, or even make it another parameter to the subroutine and stored procedure. There are plenty of other enhancements that you could also use if you feel creative.

This is just one example of how you can integrate CLR code into your SQL Server 2005 server.

Code FOR LoggingSP.vb

Option Explicit On 
' Option Strict On
Imports System
Imports System.IO
Imports Microsoft.VisualBasic 
Imports System.Diagnostics

Public Class YVlog

  Public Shared Sub LogToTextFile(LogName as String, NewMessage as String)
        Dim w As StreamWriter = File.AppendText(LogName)
        LogIt(NewMessage, w)

        ' Close the writer and underlying file.
        w.Close()
  End Sub

     Public Shared Sub LogIt(ByVal logMessage As String, ByVal w As TextWriter)
        w.Write(ControlChars.CrLf & "Log Entry : ")
        w.WriteLine("{0} {1}", DateTime.Now.ToLongTimeString(), DateTime.Now.ToLongDateString())
        w.WriteLine("  :")
        w.WriteLine("  :{0}", logMessage)
        w.WriteLine("-------------------------------")
        ' Update the underlying file.
        w.Flush()
     End Sub

  Public Shared Sub LogToEventLog (errorMessages as string)
	Dim log As System.Diagnostics.EventLog = New System.Diagnostics.EventLog
        log.Source = "My Application"
        log.WriteEntry(errorMessages)
  End Sub

End Class

  
Total article views: 23219 | Views in the last 30 days: 22
 
Related Articles
FORUM

Creating assembly

I can no longer create assemblies on the sql server

ARTICLE

How to create a CLR assembly on a remote server with limited permissions

How to create external_access CLR assembly on remote MS SQL server, when trustworthy option is forbi...

FORUM

Errors on creating assembly from global dlls

Errors on creating assembly from global dlls

FORUM

Cannot create assembly referencing system.core v3.5.0.0

Fail to create assembly using Linq

FORUM

Create GAC Assembly

How To Create Assembly from GAC and how to Deal with referenced assembly

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones