SP to rename a directory using CLR in SQL Server 2005

This article would explain in detail about the way to rename a physical directory using CLR in SQL Server 2005. The complete source code is attached with this article.

1. Open VS.NET 2005
2. Create C# based Database project
3. Right click on the solution and choose Add >> Stored proceedure
4. Copy paste the below code 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 RenameDirectory(String strOldName, String strNewName)
{
SqlPipe objPipe;
objPipe = SqlContext.GetPipe();

try
{
//Check for directory existance
//Note: Since it is for demo purpose I haven't done the complete validation
if (Directory.Exists(strNewName))
{
objPipe.Send("Specified directory already exists.");
}
else // Rename the Directory
{
//Directory.CreateDirectory(strNewName);
Directory.Move(strOldName, strNewName);
objPipe.Send("Directory has been successfully renamed.");
}
}
catch (Exception objExp)
{
objPipe.Send(objExp.Message.ToString());
}
}
};

After Building this database project in VS.NET 2005 open up SQL 2005 and do the following:

Create Assembly RenameDIRFrom 'C:\Documents and Settings\Administrator\My Documents\Visual Studio\Projects\SqlServerProject1\SqlServerProject1\bin\Debug\SqlServerProject1.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 Framework class library. Since the above code needs to access the file system, we have set EXTERNAL_ACCESS permission while creating the assembly.

Create Proc dbo.RenamePhysicalDirectory
(
@strOldPath nvarchar(1024), @strNewPath nvarchar(1024)
)
As External name RenameDIR.StoredProcedures.RenameDirectory
Go

If we try to test our work by executing the below code:
Exec dbo.RenamePhysicalDirectory 'c:\vadivel', 'c:\vadivelTesting';

It would throw an error as follows:

Msg 6263, Level 16, State 1, Line 1
Execution of user code in the .NET Framework is disabled. Use sp_configure 'clr enabled' to enable execution of user code in the .NET Framework.

The work around execute the below code snippet and then try to test our work ..

sp_configure 'clr enabled', 1
Reconfigure with override