Saturday, September 17, 2005

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
public static void RenameDirectory(String strOldName, String strNewName)
SqlPipe objPipe;
objPipe = SqlContext.GetPipe();

//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.Move(strOldName, strNewName);
objPipe.Send("Directory has been successfully renamed.");
catch (Exception objExp)

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'

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

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

No comments: