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
public partial class StoredProcedures
public static void RenameDirectory(String strOldName, String strNewName)
objPipe = SqlContext.GetPipe();
//Check for directory existance
//Note: Since it is for demo purpose I haven't done the complete validation
objPipe.Send("Specified directory already exists.");
else // Rename the Directory
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'
WITH PERMISSION_SET = EXTERNAL_ACCESS;
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