SP to create directory using CLR in SQL Server 2005

This article would explain in detail about the way to create a physical directory using CLR in SQL Server 2005. Lets get started.

1. Open a C# - Database project in Visual Studio.NET 2005
2. Create a new Stored Procedure file in it.
3. Copy-Paste the below code snippet into that file.

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlServer;
using System.Data.SqlTypes;
using System.IO;


public partial class StoredProcedures
{
[SqlProcedure]
public static void CreatePhysicalDirectory(String strPath)
{
SqlPipe objPipe;
objPipe = SqlContext.GetPipe();

try
{
//Check for directory existance
if (Directory.Exists(strPath))
{
objPipe.Send("Specified directory already exists.");
}
else // Directory creation
{
Directory.CreateDirectory(strPath);
objPipe.Send("Directory has been successfully created.");
}
}
catch (Exception objExp)
{
objPipe.Send(objExp.Message.ToString());
}
}
};

4. Deploy the stored procedure. Deployment is as simple as right clicking on the project and choosing "Deploy".
5. After deploying this via VS.NET 2005, go to SQL Server 2005 and run the below code snippet :

Exec CreatePhysicalDirectory 'c:\testFolder';

The code would fail miserably throwing the following error: Request for the permission of type 'System.Security.Permissions.FileIOPermission, mscorlib, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

The work around:

In order to make the code work follow the below steps.

Create Assembly CreateDIR
From 'C:\Documents and Settings\Administrator\My Documents\Visual Studio\Projects\SqlServerProject2\SqlServerProject2\bin\Debug\SqlServerProject2.dll'
With Permission_Set = External_Access;
Go

The assemblies imported with External_Access allows access to external sources such as file system using the .NET FCL (Framework class library).

As you know the above code needs access to the file system, we have set External_Access permission while creating the assembly.

Create Proc dbo.CreatePhysicalDirectory
(
@strPath nvarchar(1024)
)
As External name CreateDIR.StoredProcedures.CreatePhysicalDirectory
Go

Now we could test the code by executing the below code block:

Exec dbo.CreatePhysicalDirectory2 'c:\testFolder';