06 December 2006

Building my First SQL Server 2005 CLR

Greg Larsen provides a step-by-step tour of the hurdles he had to jump in building and deploying his first serious CLR stored procedure.

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:

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.

Keep up to date with Simple-Talk

For more articles like this delivered fortnightly, sign up to the Simple-Talk newsletter

Downloads

This post has been viewed 76647 times – thanks for reading.

Tags: , , , , , , ,

  • Rate
    [Total: 83    Average: 3.6/5]
  • Share

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.

View all articles by Greg Larsen

  • Anonymous

    You can store the code in the database
    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).

  • Anonymous

    SQL CLR
    * 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.

  • Anonymous

    Having a go at trying it out
    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.

  • Phil Factor

    An alternative to all this

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

  • Greg Larsen

    Excellent Alternative
    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.

  • Anonymous

    stored procedure
    how to insert into table and execute using stored procedure

  • Tony Davis

    source code management
    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.)

  • Anonymous

    Easier way to do things
    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.

  • Anonymous

    PERMISSION_SET = EXTERNAL_ACCESS
    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

  • Anonymous

    Books for 70-431
    Could you recommend a good book for 70-431 exam?

    Thank you!

  • Greg Larsen

    PERMISSION_SET = EXTERNAL_ACCESS
    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

  • Rod

    SQLCLR’s output
    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;

  • Anonymous

    How it work for not for me?
    Whenever I execute following statement:

    CREATE ASSEMBLY xp_getfiledetailsfrom ‘c:tempxp_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 ‘SEISMICMICROlli’, 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.

  • Anonymous

    CLR Procedure Schema
    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

  • ishan

    help needed!!!!!
    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

  • codedefiner

    good article
    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&gt;