CLR to create a directory / folder

The reason for this is the ridiculous security settings required to use xp_cmdshell - specifically having to use sp_xp_cmdshell_proxy_account . The issue is that this requires a domain login and password, which is problematical if your account passwords change regularly.

The way around this is to use a CLR to create the folder. The code for the CLR (assuming you know how to create a CLR project) is as follows :


public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]

public static void cproc_createfolders(string folderName)
{
//Check whether folder exists
if (!System.IO.Directory.Exists((folderName)))
{
System.IO.Directory.CreateDirectory(folderName);
}
}
};

Simple enough. Installing it isn't. As this is accessing external to SQL Server, you need to set up the necessary permissions. There are two ways of doing this, via a login or by setting the database to be trustworthy. I used the latter, as this is a database behind a firewall, internal use only so felt it was OK. If you do not do this, you will get an error along these lines :

Request for the permission of type 'System.Security.Permissions.FileIOPermission, mscorlib, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

So :

ALTER DATABASE MyDatabase SET TRUSTWORTHY ON

Next is to install the assembly. This needs to specify that it is for External Access:


CREATE ASSEMBLY CreateDIR
FROM 'C:\CLR\myCLR.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO


And finally a stored procedure to allow access from SQL :


CREATE PROCEDURE dbo.cproc_createfolder
(
@strPath Nvarchar(1024)
)
AS EXTERNAL NAME CreateDIR.StoredProcedures.cproc_createfolder



Access to this routine is via dbo.cproc_createfolder.

Took a lot of searching to find this, so I think it is only fair to credit Vadivel's Blog.