Welcome to EMC Consulting Blogs

I'm currently working on migrating a SQL Server 2000 system to SQL Server 2005 and one of the issues we have come across has been that the undocumented stored procedure xp_getfiledetails (which is being used in our SQL2000 solution) no longer exists. This sproc does exactly what it says on the tin - it provides information about a file - the same information that you see when right-clicking on a file in Windows Explorer and clicking 'Properties'.

Rather than go through the pain of moving this extended stored procedure into SQL2005 I thought I'd just build my own version of it using SQLCLR.

Given that this was the first SQLCLR sproc that I have written it too me a day or so to get up to speed on exactly what I needed to do but once I had it clear in my mind it was actually fairly easy.

I have provided a zip file as an attachment to this post that contains the following:

• Conchango.SqlServer.SqlClrToolkit.GetFileDetails.dll - The assembly (i.e. a .dll file) containing my SQLCLR stored procedure
• GetFileDetails.cs - The source code for anyone that wants to have a look at it or even change it
• ProcedureCreateScript.sql - A script for cataloging the assembly into the database and creating the stored procedure in SQL Server. I have called the stored procedure csp_getfiledetails.

A few caveats that I need to point out:

1. xp_getfiledetails returned a column called [Alternate Name]. I am yet to see this column contain anything but null and given that xp_getfiledetails is undocumented I didn't know what it should contain. Hence, csp_getfiledetails always returns null for this column as well.
2. I have cataloged the assembly in 'master'. BOL kind of warns against doing this, its up to you whether you heed the warning or not. For demo purposes I have ignored it.

And here's a screenshot of that script in SSMS along with the all-important output.

I see alot of people out on the internet that need this so hopefully Google will be their friend and lead them here.

Here's hoping it proves useful to someone!

One more thing, I want to thank my colleagues Andy Britcliffe, Jon George and Christian Wade for the little titbits of help they gave me whilst building this.

-Jamie

P.S. For those that want to see it without downloading the zip file, here's the source code:
 
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;
namespace Conchango.SqlServer.SqlClrToolkit
{
  public partial class GetFileDetails
  {
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void csp_getfiledetails(string filePath)
    {
      try
      {
        // Get a FileInfo object which will tell us everything we want to know
        FileInfo myFile = new FileInfo(filePath);
        //Define all the columns
        SqlMetaData colAlternateName = new SqlMetaData("Alternate Name", SqlDbType.NVarChar, 4000);
        SqlMetaData colSize = new SqlMetaData("Size", SqlDbType.BigInt);
        SqlMetaData colCreationDate = new SqlMetaData("Creation Date", SqlDbType.NChar, 8);
        SqlMetaData colCreationTime = new SqlMetaData("Creation Time", SqlDbType.NChar, 6);
        SqlMetaData colLastWrittenDate = new SqlMetaData("Last Written Date", SqlDbType.NChar, 8);
        SqlMetaData colLastWrittenTime = new SqlMetaData("Last Written Time", SqlDbType.NChar, 6);
        SqlMetaData colLastAccessedDate = new SqlMetaData("Last Accessed Date", SqlDbType.NChar, 8);
        SqlMetaData colLastAccessedTime = new SqlMetaData("Last Accessed Time", SqlDbType.NChar, 6);
        SqlMetaData colAttributes = new SqlMetaData("Attributes", SqlDbType.Int);
        //Define a recordset based on the column metadata
        SqlDataRecord record = new SqlDataRecord(new SqlMetaData[] {
                colAlternateName,
                colSize,
                colCreationDate,
                colCreationTime,
                colLastWrittenDate,
                colLastWrittenTime,
                colLastAccessedDate,
                colLastAccessedTime,
                colAttributes});
 
 
        //Add values to be returned
        record.SetInt64(1, myFile.Length);
        record.SetString(2, myFile.CreationTime.ToString("yyyyMMdd"));
        record.SetString(3, myFile.CreationTime.ToString("HHmmss"));
        record.SetString(4, myFile.LastWriteTime.ToString("yyyyMMdd"));
        record.SetString(5, myFile.LastWriteTime.ToString("HHmmss"));
        record.SetString(6, myFile.LastAccessTime.ToString("yyyyMMdd"));
        record.SetString(7, myFile.LastAccessTime.ToString("HHmmss"));
        record.SetInt32(8, (int)myFile.Attributes);
        SqlContext.Pipe.Send(record);
      }
      catch (Exception e)
      {
        throw (e);
      }
    }
  };
}