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 a resource 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 .
Introduction
I'VE Seen a file invocation "How do i ftp a file insto sql" Well, IF you have 6.5 or 7.0 this article shop beh helpful. Unfortunately it's not an easy answer, but it it'll Work Great Once You'VE Set IT Up. The answer is there with 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 what bad, i promise.
Download the Support 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 Up 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 Object
Set nocount on
Declare @hr int - Holds Error Value for Each SP_OA Function
Declare @opkg int - Holds the Handle of the Object
Declare @Source Varchar (255) - Hold Error Info
Declare @Description VARCHAR (255) - HOLD ERROR DESCRIPTION
Declare @Connected Int - Hold The Handle of The Internet Session
Declare @opened int - Hold The Handle of The Connection To The FTP Server
Declare @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 @opkg
EXEC @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 USURERRORS.
--Check for errors
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. - 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
.................... ..
--Connect to the FTP Server (Microsoft.com)
--VB Equivalent Connect_INTERNET (Connected, "ftp.microsoft.com",, __
- "Anonymous", "graz@sqlteam.com", 1, 0, 0)
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 final from their ftp server to the hard driving 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 did not 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!
