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 this ZIP 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: |
 Click here to copy the following block |
Set NoCount on
DECLARE @hr int
DECLARE @oPKG int
Declare @source varchar(255)
Declare @description varchar(255)
Declare @connected int
declare @opened int
declare @getfile bit |
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. |
| 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. |
| Click here to copy the following block |
IF @hr <> 0
BEGIN
PRINT '*** Create Package object failed'
EXEC sp_OAGetErrorInfo @oPKG, @source OUT, @description OUT
select @description ,@source
RETURN
END |
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. |
| The next step is to connect to an FTP site. In this case we'll connect to ftp.microsoft.com. |
| Click here to copy the following block |
Exec @hr=sp_OAMethod @oPkg,Connect_Internet,@Connected Out,
@handleid=@opened,
@server='ftp.microsoft.com',
@port=0,
@username='anonymous',
@pwd='graz@sqlteam.com',
@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. |
| Click here to copy the following block |
Exec @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!
|
|