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