Click here to monitor SSC
  • Av rating:
  • Total votes: 80
  • Total comments: 16
Greg Larsen

Building my First SQL Server 2005 CLR

06 December 2006

Although Microsoft has always advised us not to use them, if you are like me you still might have found reason to use one or more of the SQL Server 2000 undocumented features. In particular, I built a number of processes that used an undocumented, extended stored procedure (SP) called xp_getfiledetails, which allowed me to obtain various bits of operating system information for a physical disk file.

Well, in SQL Server 2005, the "bad" news is that Microsoft has decided to remove some of those undocumented features, including the xp_getfiledetails stored procedure. On the plus side, SQL 2005 does bring with it a lot of new features, one of which is the Common Language Runtime (CLR). The CLR allows you to extend SQL Server functionality by building your own routines using programming languages other than T-SQL.

So although there is no direct replacement for the extended SP xp_getfiledetails, it's possible to build a common language runtime (CLR) object that will replicate its functionality. This article describes the first CLR-based stored procedure that I have written and implemented into our SQL Server 2005 environment, and what I learned from the process.

What did xp_getfiledetails do?

As noted, the xp_getfiledetails extended SP allowed you to obtain some operating system information for a physical disk file. To execute xp_getfiledetails you passed in a file name, and the extended SP returned the following information:

  • short name
  • size
  • create date and create time
  • last written date and time
  • last access date and time
  • file attribute byte of the file

I used this extended SP in a number of my T-SQL scripts and SPs to determine whether or not a file existed and to get the size of the file.

Building my first CLR

Since I had a number of processes that called xp_getfiledetails I needed a way to replace this functionality and I wanted to avoid having to modify all my existing processes.

My solution was to build a CLR stored procedure that could transparently replace xp_getfiledetails. Microsoft has recommend that CLR code only be used in situations where they out-perform T-SQL, or where they provide functionality that is simply not available using T-SQL. It seemed to me that building a CLRbased stored procedure to replace the deprecated xp_getfiledetails would fit into the Microsoft recommendation.

Following are the steps that I followed to get the CLR stored procedure built and installed:

  1. Write the .NET (C#) code that would perform the same functionality as xp_getfiledetails
  2. Compile the .NET code
  3. Enable CLR environment in the database
  4. Create an assembly in the database based on the compiled .NET code
  5. Create a CLR stored procedure based on the assembly

Let's take a look at each one in more detail. I will focus mainly on the various hurdles I had to jump to get my C# code into SQL Server 2005 environment, and execute it.

Writing the C# code

I decided to replicate the xp_getfiledetails behavior in C#. In the interests of openness I should confess that this was not only was my first CLR stored procedure, but also my first C# program!

I'm primarily a rookie VB programmer, but I decided to write my CLR assembly using C# because I found out that C# allowed me an easy way to return an integer value completion code for my CLR sproc (as it turns out, I later I found an example of how Visual Basic could also return an integer value).

I'm not going to review my C# code in detail here, but the full source code is available for download from the "Code Download" link in the box to the right of the article title.

Compiling the C# Code

Download the C# source code and place it in a directory of your choice. In my examples, just for testing purposes, I've used c:\temp.

Before you can use the C# code in a CLR you have to compile it. I did this using the C# compiler, "csc.exe", found in the .NET framework library, and the following DOS command:

c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\csc /target:library /
out:c:\temp\xp_getfiledetails.dll c:\temp\xp_getfiledetails.cs

This compiler exe command used my C# code in the file "c:\temp\xp_getfiledetails.cs" and created a compiled DLL named "c:\temp\xp_getfiledetails.dll".

With my xp_getfiledetails DLL created, I could step into the database and, using T-SQL, complete the rest of the steps needed to define my CLR stored procedure.

Enabling the CLR

By default the CLR functionality is turned off. To enable it, you can either use the SQL Server 2005 Surface Area Configuration tool or T-SQL. Following is the T-SQL code that I used to enable the CLR on my SQL Server 2005 machine:

sp_configure 'clr enabled'1
GO
RECONFIGURE
GO

Note:
It isn't actually necessary to enable the CLR in order to create your assembly, but only to execute it.

Creating the assembly

My next step was to create an assembly in the database, based on my compiled DLL. On the face of it, this is pretty straight-forward:

CREATE ASSEMBLY xp_getfiledetails
from 'c:\temp\xp_getfiledetails.dll'
WITH PERMISSION_SET = UNSAFE

However, I gave quite a bit of thought to the following PERMISSION_SET options, which identify whether the assembly can access external resources and/or unmanaged code:

  • SAFE – allows assemblies to do only local computations and have access only to local files
  • EXTERNAL_ACCESS – similar to SAFE permissions except the assembly can access network resources and the registry
  • UNSAFE – allows unrestricted access to resources, and even allows execution of unmanaged code blocks

Using UNSAFE was not my first choice but was the option I ended up using. I tried both the SAFE and EXTERNAL_ACCESS options first, but my C# code contained a call to a pinvokeimpl method, which could only be assembled using the UNSAFE permission set.

PInvoke (platform invoke) is the means by which you can call Win32 API functions that are not directly available to the .NET framework. My C# code contained the GetShortPathName method, which is in the kernel32 DLL:

   // include GetShortPathName API to get short name of file
   [DllImport("kernel32.dll")]
   static extern int GetShortPathName(string lpszLongPath,
                                           StringBuilder lpszShortPath,
                                           int cchBuffer);

Since this is unmanaged code, the assembly that calls it requires the UNSAFE permission set. If you are concerned about UNSAFE assemblies and don't need to return the short file name then you simply can remove the GetShortPathName code and use the EXTERNAL_ACCESS permission set.

Resigned to building an UNSAFE assembly, when I first attempted to create it, I received the following error:

Msg 10327, Level 14, State 1, Line 1
CREATE ASSEMBLY for assembly 'xp_getfiledetails' failed because
assembly 'xp_getfiledetails' is not authorized for PERMISSION_SET = UNSAFE.
The assembly is authorized when either of the following is true:
the database owner (DBO) has UNSAFE ASSEMBLY permission and the database
has the TRUSTWORTHY database property on; or the assembly is signed with
a certificate or an asymmetric key that has a corresponding login with
UNSAFE ASSEMBLY permission.

I was already the DBO of the database (master) in which I was trying to assemble this code. To get around the above error in my testing environment, I set the TRUSTWORHTY property on the master database by using the following command:

ALTER DATABASE master SET trustworthy ON

However, setting TRUSTWORTHY on potentially opens the floodgates for dangerous code to run in an uninhibited manner, so on the live system this is unadvisable. Instead you will want to sign the assembly using a certificate

Creating the CLR stored procedure

Once I had successfully assembled my C# code, all that was left to create a T-SQL reference to the xp_getfiledetails method in my assembly, which would produce my stored procedure. To accomplish this I used the following command:

CREATE PROCEDURE xp_getfiledetails 
   
@FileName NVARCHAR(128)
AS
   
EXTERNAL NAME xp_getfiledetails.SQLCLR.xp_getfiledetails

I was now ready to execute my xp_getfiledetails CLR stored procedure for the first time. All I needed to do was issue the EXECUTE command just as I would have done to execute the deprecated extended SP in SQL Server 2000. Here is an example of my EXECUTE command that displays the details of a file on my c drive:

declare @return_code int
EXECUTE
@return_code = xp_getfiledetails 'c:\temp\xp_getfiledetails.cs'
print @return_code

So there you have it. There's nothing too difficult here, but yet this CLR stored procedure allowed me to add some functionality to SQL Server 2005 I would not have had otherwise. Also I was able to simulate the deprecated xp_getfiledetails extended SP without having to modify any of the existing code that referenced it.

Observations and advice

Now that I had a CLR object in my database, I realized that, for the first time, I had objects defined in my database for which the source code was stored externally. The upshot of this is that if you are going to allow CLRs then you had better develop a source code management process to make sure that you do not lose the source code associated with your CLRs. In addition to this you will need to consider where the DLLs for your CLRs should be placed. For the sake of this article, I compiled my DLL into the "c:\temp" directory. Obviously it would be wise to establish a different location for storing your CLR DLLs.

There seems to be a lot of discussion about what kinds of code/processes are appropriate for CLRs. Just because it can be done with a few lines of VB or C# code, it doesn't necessarily make it the correct design decision. At our shop we plan to move cautiously forward with CLRs. We plan to develop standards and guidelines around what is an appropriate use for CLRs and what is not. It is hoped that our standards/guidelines and testing methodology will ensure we minimize the impact that CLRs might have on our SQL Server environment.

Lastly, the DBAs I speak to are generally concerned about potential security issues associated with creating an UNSAFE assembly. My research on the web did not indicate that my assembly would open the door for any security breach, but then not all UNSAFE code is as benign as that called by my C# program. As a DBA, you will want to perform a thorough review of any CLR code that a programmer says needs to be assembled using the UNSAFE permission set.

Greg Larsen

Author profile:

Greg started working in the computer industry in 1982. In 1985, he got his first DBA job, and since then he has held five different DBA jobs and managed a number of different database management systems. Currently works as a DBA for Department of Health in Washington State managing SQL Server databases, and also does part-time consulting. He has published numerous articles in SQL Server Magazine, and many online web sites dedicated to SQL Server. He also is a SQL Server MVP and holds a number of Microsoft Certification. Greg can be reached at gregalarsen@msn.com.

Search for other articles by Greg Larsen

Rate this article:   Avg rating: from a total of 80 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: You can store the code in the database
Posted by: Anonymous (not signed in)
Posted on: Wednesday, December 06, 2006 at 8:03 AM
Message: If you deploy your assembly to a database with Visual Studio instead of just adding it from a .dll, then the source code will go into the database along with the assembly. You can do the same thing manually with the ALTER ASSEMBLY ADD FILE syntax, adding all your source code files. The .dll is stored in the database in any case, so looking after the original isn't that important - it's possible to extract it from the database (SQL Doc does it, for instance).

Subject: SQL CLR
Posted by: Anonymous (not signed in)
Posted on: Wednesday, December 06, 2006 at 8:27 AM
Message: * Couldn't figure out how to return an int from VB? How about "Return n"? :-)

* "In addition to this you will need to consider where the DLLs for your CLRs should be placed."

- it doesn't matter! - this is from Books Online: "CREATE ASSEMBLY *uploads* an assembly that was previously compiled as a .dll file from managed code for use inside an instance of SQL Server. "

...therefore it takes a copy.

Subject: Having a go at trying it out
Posted by: Anonymous (not signed in)
Posted on: Thursday, December 07, 2006 at 5:59 AM
Message: As a DBA, I've always been nervous about using CLRs. I think this is true for a lot of us who are happier with Transact SQL. I therefore enjoyed reading this article as it is has given me encouragement to 'have a go', after reading his 'warts and all' account of what happened when he tried.

Many thanks too for the comments, which will be a great help, but thanks to Greg for inspiring me to try.

Subject: An alternative to all this
Posted by: Phil Factor (view profile)
Posted on: Thursday, December 07, 2006 at 7:19 AM
Message:

I enjoyed this too, but my reaction was to think that it really wasn't necessary to use the CLR in this case if all you want to do is to get a file's details. I realise that this is beside the point, as the real interest is to hear of one man's struggle with BOL, but here is my code anyway....

CREATE PROCEDURE spFileDetails

@Filename VARCHAR(100)

/*
spFileDetails 'c:\autoexec.bat'
*/
AS
DECLARE 
@hr INT,         --the HRESULT returned from 
       
@objFileSystem INT,              --the FileSystem object
       
@objFile INT,            --the File object
       
@ErrorObject INT,        --the error object
       
@ErrorMessage VARCHAR(255),--the potential error message
       
@Path VARCHAR(100),--
       
@ShortPath VARCHAR(100),
       
@Type VARCHAR(100),
       
@DateCreated datetime,
       
@DateLastAccessed datetime,
       
@DateLastModified datetime,
       
@Attributes INT,
       
@size INT



SET 
nocount ON

SELECT 
@hr=0,@errorMessage='opening the file system object '
EXEC @hr sp_OACreate 'Scripting.FileSystemObject',
                                       
@objFileSystem OUT
IF @hr=SELECT @errorMessage='accessing the file '''
                                       
+@Filename+'''',
       
@ErrorObject=@objFileSystem
IF @hr=EXEC @hr sp_OAMethod @objFileSystem,
         
'GetFile',  @objFile out,@Filename
IF @hr=
       
SELECT @errorMessage='getting the attributes of '''
                                       
+@Filename+'''',
       
@ErrorObject=@objFile
IF @hr=EXEC @hr sp_OAGetProperty 
             
@objFile'Path'@path OUT
IF @hr=EXEC @hr sp_OAGetProperty 
             
@objFile'ShortPath'@ShortPath OUT
IF @hr=EXEC @hr sp_OAGetProperty 
             
@objFile'Type'@Type OUT
IF @hr=EXEC @hr sp_OAGetProperty 
             
@objFile'DateCreated'@DateCreated OUT
IF @hr=EXEC @hr sp_OAGetProperty 
             
@objFile'DateLastAccessed'@DateLastAccessed OUT
IF @hr=EXEC @hr sp_OAGetProperty 
             
@objFile'DateLastModified'@DateLastModified OUT
IF @hr=EXEC @hr sp_OAGetProperty 
             
@objFile'Attributes'@Attributes OUT
IF @hr=EXEC @hr sp_OAGetProperty 
             
@objFile'size'@size OUT


IF @hr<>0
       
BEGIN
       DECLARE 
               
@Source VARCHAR(255),
               
@Description VARCHAR(255),
               
@Helpfile VARCHAR(255),
               
@HelpID INT
       
       EXECUTE 
sp_OAGetErrorInfo  @errorObject
               
@source output,@Description output,
                               
@Helpfile output,@HelpID output

       
SELECT @ErrorMessage='Error whilst '
                               
+@Errormessage+', '
                               
+@Description
       
RAISERROR (@ErrorMessage,16,1)
       
END
EXEC 
sp_OADestroy @objFileSystem
EXEC sp_OADestroy @objFile
SELECT [Path]=  @Path,
       
[ShortPath]=    @ShortPath,
       
[Type]@Type,
       
[DateCreated]=  @DateCreated ,
       
[DateLastAccessed]=     @DateLastAccessed,
       
[DateLastModified]=     @DateLastModified,
       
[Attributes]=   @Attributes,
       
[size]@size
RETURN @hr


GO

Subject: Excellent Alternative
Posted by: Greg Larsen (view profile)
Posted on: Thursday, December 07, 2006 at 2:02 PM
Message: Phil your T-SQL solution is an excellent alternative to my CLR is excellent. With a few minor changes to your code it will accomplish exactly the same thing I did with my CLR. Thank you for sharing.

Subject: stored procedure
Posted by: Anonymous (not signed in)
Posted on: Friday, December 08, 2006 at 8:37 AM
Message: how to insert into table and execute using stored procedure

Subject: source code management
Posted by: Tony Davis (view profile)
Posted on: Monday, December 11, 2006 at 5:29 AM
Message: I had a chat with Adam Machanic about this and basically, it seems that this isn't really an issue with CLRs. As soon as you run CREATE ASSEMBLY all links between the DLL file and the database are gone -- SQL Server "sucks in" the bytes and stores them internally. So there is no reason to worry about the DLL. You can move it, rename it, delete it, whatever. SQL Server doesn't maintain any kind of reference to the file.

That's one of the biggest benefits of SQLCLR over, say, XPs -- with an XP you had to keep the DLL around, and if you had some stored procedures that relied on it, doing something like moving the database to a different server would require also moving the XP. Not so with SQLCLR assemblies, which are backed up, restored, and even replicated just like any other code units.

Cheers,

Tony D (Simple-Talk Ed.)

Subject: Easier way to do things
Posted by: Anonymous (not signed in)
Posted on: Wednesday, December 13, 2006 at 12:33 PM
Message: There is actually an easier way to do things. Create a database project in VS 2005 and add a stored procedure item. Using this method, you can also use VS2005 to build and deploy the stored proc, user defined function etc etc to the database with one button click. You can also enable clr debugging on sql server and step through the code if you chose but this not recommended on any production server as it severely limits the db.

Subject: PERMISSION_SET = EXTERNAL_ACCESS
Posted by: Anonymous (not signed in)
Posted on: Friday, December 15, 2006 at 12:38 PM
Message: Thanks very much for this article and the comments. It's sure great to be able to profit painlessly from others attempts :)

I did manage to find another blog entry where he does a similar thing but is able to use the PERMISSION_SET = EXTERNAL_ACCESS. I believe that it's because of a different implementation inthe C# assembly.

http://blogs.conchango.com/jamiethomson/archive/2006/08/24/4400.aspx

Subject: Books for 70-431
Posted by: Anonymous (not signed in)
Posted on: Friday, December 22, 2006 at 3:25 PM
Message: Could you recommend a good book for 70-431 exam?

Thank you!

Subject: PERMISSION_SET = EXTERNAL_ACCESS
Posted by: Greg Larsen (view profile)
Posted on: Tuesday, December 26, 2006 at 11:52 AM
Message: Jamie's version doesn't require PERMISSION_SET = UNSAFE because he doesn't use the GetShortPathName method to return the true short file name (Alternate Name), for files that have names longer that 8 character. I think you will find that Jamie's version works fine as long as you don't need the short version name

Subject: SQLCLR's output
Posted by: Rod (not signed in)
Posted on: Tuesday, June 12, 2007 at 10:31 AM
Message: I'm doing some XML transformations and trying to get just plain text. Any clue on how to return a large string right form a SQLCLR and into SQL?

My function is:
create function dbo.TransformaTXT( @inXML xml, @inXSLT xml )
returns [??????]<-
as external name [myassm].XSLTTransform].TransformaTXT;

Subject: How it work for not for me?
Posted by: Anonymous (not signed in)
Posted on: Wednesday, October 10, 2007 at 2:28 PM
Message: Whenever I execute following statement:

CREATE ASSEMBLY xp_getfiledetailsfrom 'c:\temp\xp_getfiledetails.dll'WITH PERMISSION_SET = UNSAFE

The messages I got is:
Msg 15404, Level 16, State 19, Line 2
Could not obtain information about Windows NT group/user 'SEISMICMICRO\lli', error code 0x5.

The reason is because of PERMISSION_SET = UNSAFE. By the way, I have set TRUSTWORTHY TO 1. You also can email me lli@seismicmicro.com. Thanks.

Subject: CLR Procedure Schema
Posted by: Anonymous (not signed in)
Posted on: Thursday, February 28, 2008 at 5:23 AM
Message: Hi there.
For any of you guys battling to release you procedure to a specific schema in the DB. The schema your procedure is created or updated in relates to the default schema of the connection you are using for your DB.
So if your DB connections default SCHEMA is "myNewScema" your procedure will look a bit like this [myNewSchema].[myNewProcedure].

THough this may help someone as I spent a fair bit of time trying to figure it out even though it is a bit obvious.

Cheers

Subject: help needed!!!!!
Posted by: ishan (view profile)
Posted on: Thursday, February 12, 2009 at 10:13 AM
Message: i read the code but coudn't understand it .. neways i tried another code but its showing errors . can u tell me what i am missing here

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefined
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString.GetOSVersion()
{
return System.Environment.OSVersion.Tostring()
}
}

i created it in visual studio 2005

Subject: good article
Posted by: codedefiner (view profile)
Posted on: Sunday, September 13, 2009 at 9:42 AM
Message: this is great article, you can find more at <a href="http://www.codedefiner.com/vbnet/CLRinSQL.aspx">http://www.codedefiner.com/vbnet/CLRinSQL.aspx</a>

 

Phil Factor
Searching for Strings in SQL Server Databases

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

 View the blog

Top Rated

SQL Server XML Questions You Were Too Shy To Ask
 Sometimes, XML seems a bewildering convention that offers solutions to problems that the average... Read more...

Continuous Delivery and the Database
 Continuous Delivery is fairly generally understood to be an effective way of tackling the problems of... Read more...

The SQL Server Sqlio Utility
 If, before deployment, you need to push the limits of your disk subsystem in order to determine whether... Read more...

The PoSh DBA - Reading and Filtering Errors
 DBAs regularly need to keep an eye on the error logs of all their SQL Servers, and the event logs of... Read more...

MySQL Compare: The Manual That Time Forgot, Part 1
 Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.