Working with a complex SQL environment which used a multitude of technologies, one of the problems we encountered was using SQL Jobs to transfer files via FTP.
The existing SQL Jobs in our environment used an external application (the command line application ftp.exe) called via the
xp_cmdshell extended stored procedure, to transfer the files to the FTP servers.
It worked most of the time but when it didn't there was no easy way to trace the failures directly within SQL.
Transact-SQL can be powerful and fun to use, but it has some limitations and lacks the advantages of managed code.
The solution I've provided allows for better handling of code... Drum roll for CLR Stored Procedures...
CLR Stored Procedures
Defining a CLR Stored Procedure allows one to utilise managed code from within SQL. This gives almost infinite possibilities in extending the functionality of SQL Stored Procedures by leveraging the .NET Framework with your favorite language such as C# or VB.NET.
By using a CLR Stored Procedure which calls the function defined in the pre-compiled .NET DLL, we were able to transfer files via FTP to and from FTP Servers directly from a SQL stored procedure and catch and handle exceptions and finally return the results to SQL variables.
Leveraging this, we used a trace table to record when a file was successfully uploaded or downloaded and if not we recorded the error to assist with debugging/troubleshooting for the SQL Database Administrators.
This solution was inspired by the article CLR Stored Procedure and Creating It Step by Step which was written by Virat Kothari.
This is a high-level overview of the steps taken to create the CLR Stored Procedure.
- Create a Visual Studio Project as a Class Library.
- Decorate functions to be exposed with [
- Compile Project in and copy .DLL file to the appropriate folder.
- Register Assembly from within SQL Server Management Studio:
CREATE ASSEMBLY clr_sqlFTP AUTHORIZATION dbo
WITH PERMISSION_SET = UNSAFE
- Create the CLR Stored Procedure:
CREATE PROCEDURE ftpDownload
@result nvarchar(MAX) OUTPUT
AS EXTERNAL NNAME clr_sqlFTP.[sqlFTP.FTP].ftpDownload
- Execute the CLR Stored Procedure as per any normal Stored Procedure and save the results to a Trace Table.
Using the Code
I have provided the source and the compiled solution which will allow anyone who wants a quick fix or who wants to learn more about the solution to get started with CLR Stored Procedures and a FTP Solution which works directly within Microsoft SQL.
The FTP functionality is provided by the .NET class
FtpWebRequest which inherits
By executing the CLR Stored Procedure and saving the results into a table, you can record the results of the upload or download.
Installing SQL FTP Solution
If you download the compiled solution, ensure you copy the .dll file to the c:\windows\system32 folder or modify the location of the file in the
Create Assembly statement.
To enable CLR within a SQL database, use the following:
sp_configure 'clr enable', 1
ALTER DATABASE <databasename> SET TRUSTWORTHY ON
Points of Interest
Problems don't create solutions, they create a will for solutions, people create the solutions.
I hope I've created a solution which you can benefit from.
This is the first version of this article and I welcome any information or advice to better the solution provided.