Using FTP in Transact-SQL

Using FTP in Transact-SQLGuestAuthor on 10/1/2001 in Stored Procsmfemenel sent us a great article on how you can FTP a file using T-SQL. He writes "The following article is based on aresource I found at 15Seconds.com but will be helpful here for all you SQL Developers. The article assumes a bit of existing VB knowledge, I’ll attempt to make this one useful for “everyman(woman)”. I’ve included the compiled DLL file, so if you don’t want to mess around with VB, there’s no need to, you can skip straight to registering the DLL on your server.

---

Introduction

I’ve seen a few posts asking “How do I ftp a file into SQL” Well, if you have 6.5 or 7.0 this article should be helpful. Unfortunately it’s not an easy answer, but it’ll work great once you’ve set it up. The answer is that there isn’t a function in SQL to let you do this, you need to create a DLL to help you. For those of you who just hit the floor, get up, dust yourself off, take a deep breath and keep going. It’s not that bad, I promise.

Download the suppport files

There are a few files for you to download which go along with this article. (Note: All the files are in thisZIP file.) The first one, FTP_SQLDLL.cls is the VB file we’ll use to create our FTP_SQLTEAM.dll. Our second one is SQLTeam_FTP.sql which is what we’ll use in Query Analyzer to execute our compiled DLL. Also included in the zip file is the DLL itself (FTP_SQLTEAM.dll), all compiled and ready to go, should you not feel like building it yourself. I’ve also added a Word document called “API Notes” which contains a little more detail about the VB side of creating the DLL that didn’t fit into a SQL Server article.

The DLL

The fine folks at Microsoft have a DLL called “WinInet”. It provides you with internet functionality. We’re going to make some API calls to that DLL and take advantage of their code. Let’s think about this for a second. Using this method, you will be able to make API calls from TSQL!

To register the DLL, follow these steps. Copy the FTP_SQLTEAM.dll to your server. I prefer to keep my DLL files in Winnt/System folder. Now, from a command line, type in Regsvr32.exe/DLLNAME.dll to register the dll.

The Transact-SQL Script

From Query Analyzer or a text editor go ahead and open up the SQLTeam_FTP.sql file so we can go through the details:

In the first section, we’re not doing anything new, just setting up some variables to catch the various things that we need in our script:

--Create an instance of FTP ObjectSet NoCount onDECLARE @hr int--Holds error value for each sp_OA functionDECLARE @oPKG int--Holds the handle of the objectDeclare @source varchar(255)--Hold error infoDeclare @description varchar(255)--Hold error descriptionDeclare @connected  int--Hold the handle of the Internet sessiondeclare @opened int--Hold the handle of the connection to the FTP Serverdeclare @getfile bit--Result of the success(1)/Failure(2) of the getfile operation

Our next block of code is creating an instance of the object. We’re going to store the object token, which is just an integer identifying the created object in the variable @oPKG. We’ll use it everytime we run a method or property setting, so the functions know which object (were we to have more than one) we’re talking about. Let’s go through this line by line.

Here, we’re going to tell SQL to create an instance of the FTP object we created (using sp_OACreate) and store it’s “object id” in a variable called @oPkg.

--First, we want to create the object and store it's handle in @oPKGEXEC @hr = sp_OACreate 'FTP_SQLTeam.FTP_SQLDll', @oPKG OUT

Our value @hr will catch the return code of the sp_OACreate method. If the return code is 0 then we were successful in creating an instance of our object. Any other return value is a failure. I find it extremely useful, at least on our initial script set up, to put in a print line so we know which block is giving us our errors.

--Check for errorsIF @hr <> 0BEGIN    PRINT '***  Create Package object failed'    EXEC  sp_OAGetErrorInfo @oPKG, @source OUT, @description OUTselect @description ,@source    RETURNEND

Now that we have an instance of our object created, we can start putting it to work by invoking those Public Methods we created for it in our DLL. If you’re used to coding in VB I’ve put in a reference to what the VB syntax would be. Let’s look at that first line as we’ll use it a few more times for each of our subsequent functions.

We’re going to execute a method of our object (Open_Internet). First, we pass it the object id from when we created the object(@oPkg), then pass the method name “Open_Internet”, then a variable to catch any return parameter from the method call. If there is no return value, you can just use “null”, then we specify the parameters the function expects. Here is a big caveat. The parameters are not like parameters you use in TSQL. Query Analyzer does not see @alias as a sql variable. You must use the same name as your original function declaration expects.

--Establish our internet connection-- VB Equivalent=Open_Internet("myftp", 1, vbNullString, vbNullString, 0)Exec @hr=sp_OAMethod @oPkg,Open_Internet,@opened Out,@alias='myftp',@accesstype=1,@proxy="",@bypass="",@flags=0

The next step is to connect to an FTP site. In this case we'll connect to ftp.microsoft.com.

--Connect to the FTP Server(microsoft.com)--vb Equivalent Connect_Internet(connected, "ftp.microsoft.com", , __--"anonymous", "[e-mail]", 1, 0, 0)Exec @hr=sp_OAMethod @oPkg,Connect_Internet,@Connected Out,@handleid=@opened,@server='ftp.microsoft.com',@port=0,@username='anonymous',@pwd='[e-mail]',@service=1,@flags=0,@context=0

And finally we can copy a file from their ftp server to the hard drive of the local machine.

--Get the file and direct it to our local drive--vb Equivalent Get_File(sessionid, remotefile, newfile, _--failifexists, flagsandattr, flags, context) As BooleanExec @hr=sp_OAMethod @oPkg,Get_File,@getfile OUT,@sessionid=@Connected,@remotefile='/bussys/readme.txt',@newfile='c:/msreadme.txt',@failifexists=0,@flagsandattr=0,@flags=1,@context=0

Conclusion

That should do it! Remember, I’ve kept this pretty simple so it was easy to follow. You can make it a much more robust script by adding parameters for the file name, the server, userid, pwd, etc. The article on 15seconds.com points out several other function calls you can make that I didn’t go into. Here’s another link that details what functions are available in WinInet.dll. Get your feet wet with this version first. Then you can add in the fancier stuff. Walk before you run!