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.