Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Site Related Forums
 Article Discussion
 Article: Using FTP in Transact-SQL

Author  Topic 

AskSQLTeam
Ask SQLTeam Question

0 Posts
Posted - 2001-09-30 : 21:21:43
mfemenel 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’t want to mess around with VB, there’s no need to, you can skip straight to registering the DLL on your server.

Article Link.

rrb
SQLTeam Poet Laureate

1479 Posts
Posted - 2002-02-27 : 17:27:33
quote:

No need to bother with a .dll!

I implemented a solution based on an article from Microsoft. It works like a champ! (and it's easy to configure/maintain too!).

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro99/html/SQL99D1.asp

Anybody know where to find this article now? Microsoft have moved it...

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"
Go to Top of Page

izaltsman
A custom title

1139 Posts
Posted - 2002-02-27 : 18:19:01
I think they pulled it off MSDN 'cause now Microsoft wants you to use FTP task in SQL2k's DTS instead.

If you don't want to go DTS route though, here is a VERY brief summary of the article:
Every version of Windows comes with a command-line ftp utility. This ftp client can work either in interactive mode, or it can use a pre-built batch file. Therefore, using xp_cmdshell your SQL Server could spit out a bunch of FTP commands into a file, and then tell the ftp client to go run that file...


Go to Top of Page

rrb
SQLTeam Poet Laureate

1479 Posts
Posted - 2002-02-27 : 18:35:07
Ah OK, I was hoping this article was going to tell me something I didn't know....

Thanks for your help.

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"
Go to Top of Page

axisperfect
Starting Member

21 Posts
Posted - 2005-02-02 : 20:34:10
I managed to connect to my server using this DLL, but due to routing/firewall settings, I need to set my FTP connections to Passive mode. I checked up on some documentation on WinInet APIs and found that a flag INTERNET_FLAG_PASSIVE (0x08000000) can be sent to InternetConnect (as @flags second last parameter) to indicate Passive FTP connection.

I've tried 0x08000000, '0x08000000' and 08000000 (which ran the stored procedure fine, but would die on a directory listing), but I just don't know how to convert the flag value so that the stored procedure will accept it. Help?
Go to Top of Page

axisperfect
Starting Member

21 Posts
Posted - 2005-02-03 : 01:05:22
I hacked a solution to my Passive mode problem by modifying the .cls file and compiling a new DLL. If anyone's in the same situation, here's what I did.

Added: 
Private Const INTERNET_PASSIVE_FLAG = &H8000000

Modified:

'Takes the handle from the above InternetOpen function and connects to an FTP Server.
'It will return a 0 if it fails, otherwise, it will return the handle of your FTP Session.
'flags = 0 (nothing), flags = 1 (Passive mode)
Public Function Connect_Internet(handleid, server, port, username, pwd, service, flags, context) As Long
    If flags = 0 Then
        Connect_Internet = InternetConnect(handleid, server, port, username, pwd, service, flags, context)
    Else
        Connect_Internet = InternetConnect(handleid, server, port, username, pwd, service, INTERNET_PASSIVE_FLAG, context)
    End If
End Function


In t-SQL, to make a Passive FTP connection:


	Exec @hr=sp_OAMethod @oPkg,Connect_Internet,@Connected Out,
		@handleid=@opened,
		@server='server.com',
		@port=0,
		@username='username',
		@pwd='password',
		@service=1,
		@flags=1, -- 0 : active, 1 : pasv
		@context=0
Go to Top of Page

ws5926
Yak Posting Veteran

88 Posts
Posted - 2005-11-10 : 11:46:28
How do I set it up to use to PUT command instead of the GET command?

Live to Throw
Throw to Live
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2005-11-11 : 01:14:54
quote:
Originally posted by ws5926

How do I set it up to use to PUT command instead of the GET command?

Live to Throw
Throw to Live

See if this helps you
http://www.nigelrivett.net/FTP/s_ftp_PutFile.html

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

ws5926
Yak Posting Veteran

88 Posts
Posted - 2005-11-11 : 07:53:28
I don't want to cmdshell. That was the whole point of writing this dll.

Live to Throw
Throw to Live
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts
Posted - 2005-11-11 : 19:53:05
The link in the original article has a PUT example.
Go to Top of Page

jstrydom
Starting Member

1 Post
Posted - 2007-11-16 : 03:56:10
Great article

Only one problem, when I run the code in the example to get the 'readme.txt' file, it runs, but the file is not in the directory specified. What am I doing wrong ?
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2007-11-19 : 03:08:42
quote:
Originally posted by jstrydom

Great article

Only one problem, when I run the code in the example to get the 'readme.txt' file, it runs, but the file is not in the directory specified. What am I doing wrong ?

It should be in the server's directory and not at the client system's directory

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

ccastillo
Starting Member

1 Post
Posted - 2008-01-22 : 18:54:32
I have been using this for 6 months now. I suddenly got an error on 1/9/08.
Error -2147023782
A dynamic link library (DLL) initialization routine failed.
ODSOLE Extended Procedure

Any idea what i can do to fix this? Has something changed?
Go to Top of Page

hans.edt
Starting Member

2 Posts
Posted - 2008-11-24 : 23:58:58
Enterprise Distributed Technologies have two products, edtFTPnet/Express and edtFTPnet/PRO, that allow you to do FTP, FTPS and SFTP operations in SQL CLR. They start at $149.
Go to Top of Page

hans.edt
Starting Member

2 Posts
Posted - 2008-11-25 : 00:02:58
Oops I forgot to include links to these products:

    * [url]http://www.enterprisedt.com/products/edtftpnetexpress/overview.html[/url]
    * [url]http://www.enterprisedt.com/products/edtftpnetpro/overview.html[/url]
Go to Top of Page

agusmer
Starting Member

1 Post
Posted - 2009-07-30 : 14:45:53
hey, i have used exactly as it says and it works, but the file i download from my ftp is a .exe file, and when i try to open it, it doesnt work, it has the properties of a DOS file.. any ideas?
thanks!
Go to Top of Page

TallPaulInJax
Starting Member

5 Posts
Posted - 2010-09-03 : 21:29:37
When I execute this, I get:

Msg 1038, Level 15, State 4, Procedure spGetFileViaFtpAndStoreLocally, Line 54
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Add a name or single space as the alias name.
Msg 1038, Level 15, State 4, Procedure spGetFileViaFtpAndStoreLocally, Line 55
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Add a name or single space as the alias name.

This refers to the two lines:
@proxy="",
@bypass="",
When I change those to:
@proxy="A",
@bypass="B",
it all works fine. Not being a SQL expert, anybody have any idea what the CORRECT thing to do is here?
Go to Top of Page

TallPaulInJax
Starting Member

5 Posts
Posted - 2010-09-04 : 00:22:17
One more thing. I have changed this into a stored procedure and added a parameter to say whether the file stored locally should be unzipped. Yet when I implement this, the utility I am using from the command line via xp_cmdshell is giving me a CRC error. Example:

IF @ZIPPED_FILES_SHOULD_BE_UNZIPPED <> 0
  BEGIN
    DECLARE @ARG VARCHAR(300)
    SET @ARG = 'IZArce -e '+@FULL_LOCAL_PATH_AND_FILENAME_TO_SAVE_AS
    EXEC @hr=master..xp_cmdshell @ARG
    print 'hr='+@hr
    IF @hr<>0 
      PRINT 'Unable to unzip file'+@FULL_LOCAL_PATH_AND_FILENAME_TO_SAVE_AS
  END


Gives me:
ERROR File fails CRC check.

I am thinking this might be because control was sent back to SQL to quickly, that the FTP transfer has not yet completed. Possible? If so, is there a way of making SQL wait for the full FTP load?
Go to Top of Page

TallPaulInJax
Starting Member

5 Posts
Posted - 2010-09-04 : 11:39:47
I've read up on the CRC error and it may be because the transfer is in ASCII instead of binary? Is there a flag to force the FTP transfer in WinInet.dll to be in binary?
Go to Top of Page

TallPaulInJax
Starting Member

5 Posts
Posted - 2010-09-04 : 11:57:18
Whilst I'm talking to myself... :-) I made the following change and it SEEMS the file was transfered in binary mode because the unzip worked correctly:

	Exec @hr=sp_OAMethod @oPkg,Get_File,@getfile OUT,
		@sessionid=@Connected,
		@remotefile=@REMAINDER_OF_PATH_AND_FILENAME_TO_RETRIEVE,
		@newfile= @FULL_LOCAL_PATH_AND_FILENAME_TO_SAVE_AS,
		@failifexists=0,
		@flagsandattr=0,
		@flags=2,  -- ??? 1=ASCII mode, 2= BINARY mode?
		@context=0
Go to Top of Page

TallPaulInJax
Starting Member

5 Posts
Posted - 2010-09-04 : 17:38:04
Ok, one more SLIGHT problem! :-) This portion of the code seems to always have @hr=0 even if the file was not found on the FTP site. What's the workaround for that?

IF @hr <> 0 --Paul: it's seems to ALWAYS be 0, even if the file doesn't exist on the FTP site.
	BEGIN
		Print 'Error in Get File Method'
		EXEC sp_oaGeterrorinfo @oPkg, @hr
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site