HOW TO CREATE A BASIC CLR FUNCTION TO EXECUTE AS A SP IN SQL SERVER

  • Article

The objective of this article is to help understand and create a CLR function to be executed as an SP in SQL Server. This with reference to the blog: https://blogs.msdn.com/sqlsecurity/archive/2008/01/10/xp-cmdshell.aspx for using CLR function as a work-around of using xp_cmdshell.

The steps involved can be broadly classified as:

1. Get the .Net Code ready with the logic of what the code is supposed to do

2. Create an assembly in SQL Server and link to the dll created as part of Step 1

3. Create a SP to execute the code

Steps Involved:

1. We created a new Visual Studio Project of type ‘Class Library’

2. We named the Project as FileOperations

3. We wrote the below code: (We are trying to copy the contents of one physical file to another file)

Imports System

Imports System.IO

Public Class MyWork

<Microsoft.SqlServer.Server.SqlProcedure()> _

Public Shared Sub SomeFileCopy()

Dim Source As String = "C:\Source.dat"

Dim Destination As String = "D:\Destination.dat"

File.Delete(Destination)

File.Copy(Source, Destination)

End Sub

End Class

4. Here we have given the name of the class as MyWork

5. Hence the details become as follows:

  • Name of the Project (Namespace) and Assembly :- FileOperations
  • Name of the class :- MyWork

6. We Build the solution in Visual Studio and collect the ‘FileOperations.dll’ file created and place it under a customer location (e.g. D:\ drive)

7. We then connect to SQL Server using SQL Server Management Studio and create an Assembly

CREATE ASSEMBLY FileOperations FROM 'D:\FileOperations.dll';
GO

8. Since the CLR function’s operation is to copy contents of one file to other, under ‘Permission set’ either give ‘Unrestricted’ / ‘External access’

clip_image002

9. Now it would be visible under Databases\<DBName>\Programmability\Assemblies in SQL Server Management Studio

clip_image004

10. We then create the SP to execute the .Net code as follows:

CREATE PROC sp_CopyFile as
EXTERNAL NAME FileOperations.[FileOperations.MyWork].SomeFileCopy
GO

Note:

The EXTERNAL NAME: FileOperations.[FileOperations.RagsWork].SomeFileCopy is of the format:-

<assembly name>.[<NameSpace Name>.<Class Name>].<Method Name>

11. Exec sp_CopyFile

 

Raghu Gopalakrishnan
SE, Microsoft SQL Server