Click here to Skip to main content
Click here to Skip to main content
Go to top

FTP directly in SQL (Using SQL CLR)

, 6 May 2011
Rate this:
Please Sign up or sign in to vote.
Using FTP via CLR in SQL

Introduction

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.

Getting Started

This is a high-level overview of the steps taken to create the CLR Stored Procedure.

  1. Create a Visual Studio Project as a Class Library.
  2. Decorate functions to be exposed with [Microsoft.SqlServer.Server.SqlFunction].
  3. Compile Project in and copy .DLL file to the appropriate folder.
  4. Register Assembly from within SQL Server Management Studio:
    CREATE ASSEMBLY clr_sqlFTP AUTHORIZATION dbo
    FROM 'c:\Windows\System32\sqlFTP.dll'
    WITH PERMISSION_SET = UNSAFE 
  5. Create the CLR Stored Procedure:
    CREATE PROCEDURE ftpDownload
           @ftp_server nvarchar(MAX),
           @ftp_user nvarchar(MAX), 
           @ftp_pwd nvarchar(MAX), 
           @local_file nvarchar(MAX),
           @rmt_file nvarchar(MAX),
           @ftp_mode int,
           @result nvarchar(MAX) OUTPUT
     AS EXTERNAL NNAME clr_sqlFTP.[sqlFTP.FTP].ftpDownload
  6. 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 WebRequest.

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
GO
RECONFIGURE
GO
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.

History

This is the first version of this article and I welcome any information or advice to better the solution provided.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Justin Porteous
Software Developer
South Africa South Africa
No Biography provided

Comments and Discussions

 
SuggestionOnly a small issue PinmemberMember 977766116-Apr-14 7:19 
GeneralYes sir, we did benefit Pingroupjammy_VB.net10-Apr-14 5:22 
QuestionFTP transfer to SQL Pinmemberdeveloper.cz25-Oct-11 22:38 
GeneralGreat article! PinmemberVirat Kothari8-May-11 20:43 
GeneralRe: Great article! PinmemberJustin Porteous11-May-11 20:45 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140916.1 | Last Updated 6 May 2011
Article Copyright 2011 by Justin Porteous
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid