Using FTP in transact-SQL

Source: Internet
Author: User
Tags ftp site
Using FTP in transact-SQL
Guestauthor on 10/1/2001 inStored procs
Mfemenel sent us a great article on how you can ftp a file using T-SQL. He writes"The following article is based on
Resource I found
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 got Ded the compiled DLL file, so if you don't want to mess around
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 shocould be helpful. unfortunately it's not an easy answer, but it'll work great once you 've ve set it up. the answer is that there isn' t a function in SQL
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 encoded in the ZIP file is the DLL itself (ftp_sqlteam.dll), all compiled and ready to go, shocould 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
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 re going to make some API cballs 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 cballs 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. dllTo 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 varous 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 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 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 wocould 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 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", "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 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 shoshould 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
Callyou 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!

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.