SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Using FTP in Transact-SQL
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 09/30/2001 :  21:21:43  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
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.

MuffinMan
Posting Yak Master

USA
107 Posts

Posted - 10/01/2001 :  13:11:41  Show Profile  Reply with 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

Go to Top of Page

izaltsman
A custom title

USA
1139 Posts

Posted - 10/01/2001 :  14:50:30  Show Profile  Send izaltsman an AOL message  Send izaltsman an ICQ Message  Reply with Quote
Well, it all depends on what you are looking for. Using WinInet APIs as described in Mike's article will allow you to build some robust error handling into your FTP process. On the other hand, scripting FTP commands, as described in the MSDN article will allow for an easy solution, but you will never know if your FTP jobs are failing(unless you build a separate procedure to verify the results).

One caveat about using WinInet... I was once told it might cause memory leak problems. But personally I've used WinInet several times, and never encountered any problems.

Edited by - izaltsman on 10/01/2001 14:51:21
Go to Top of Page

p2bl
Yak Posting Veteran

China
54 Posts

Posted - 10/03/2001 :  10:17:44  Show Profile  Visit p2bl's Homepage  Send p2bl an ICQ Message  Reply with Quote
I think if u have to complete a job like this,why not using extended procedure

Go to Top of Page

mfemenel
Professor Frink

USA
1421 Posts

Posted - 10/03/2001 :  10:26:33  Show Profile  Visit mfemenel's Homepage  Reply with Quote
You could also use an XP, however, you'd have to know C/C++ to write it. VB is more user friendly and accessible.

Also, XP's run in the same space as SQL server, so a poor design could crash your server.

Mike
"Anyone think someone else is stupid, he/she is the stupid. "
Go to Top of Page

GreatInca
Posting Yak Master

USA
102 Posts

Posted - 11/06/2001 :  15:43:19  Show Profile  Visit GreatInca's Homepage  Send GreatInca an AOL message  Send GreatInca a Yahoo! Message  Reply with Quote
XPs execute faster though. Anybody know where to get an FTP extended procedure?

DTS has FTP too but the filenames have to be fixed.

Go to Top of Page

robvolk
Most Valuable Yak

USA
15665 Posts

Posted - 11/06/2001 :  15:56:32  Show Profile  Visit robvolk's Homepage  Reply with Quote
You'd have to write your own extended procedure, or do a lot of internet searching for one.

It's extremely unlikely that any performance improvement would be noticeable, since the core API functions you'd be using in an extended procedure vs. a COM object are the same, unless you put in a lot of extraneous code to check status, bytes transferred, etc. You might get better performance by writing your own TCP stack and multi-threaded processes, but if you can do that then you won't need to search for an FTP extended procedure.

Go to Top of Page

rrb
SQLTeam Poet Laureate

Australia
1479 Posts

Posted - 02/27/2002 :  17:27:33  Show Profile  Reply with Quote
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

USA
1139 Posts

Posted - 02/27/2002 :  18:19:01  Show Profile  Send izaltsman an AOL message  Send izaltsman an ICQ Message  Reply with Quote
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

Australia
1479 Posts

Posted - 02/27/2002 :  18:35:07  Show Profile  Reply with Quote
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 - 02/02/2005 :  20:34:10  Show Profile  Reply with Quote
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 - 02/03/2005 :  01:05:22  Show Profile  Reply with Quote
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

USA
88 Posts

Posted - 11/10/2005 :  11:46:28  Show Profile  Reply with Quote
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

India
22755 Posts

Posted - 11/11/2005 :  01:14:54  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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

USA
88 Posts

Posted - 11/11/2005 :  07:53:28  Show Profile  Reply with Quote
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

USA
15665 Posts

Posted - 11/11/2005 :  19:53:05  Show Profile  Visit robvolk's Homepage  Reply with Quote
The link in the original article has a PUT example.
Go to Top of Page

jstrydom
Starting Member

1 Posts

Posted - 11/16/2007 :  03:56:10  Show Profile  Reply with Quote
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

India
22755 Posts

Posted - 11/19/2007 :  03:08:42  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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 Posts

Posted - 01/22/2008 :  18:54:32  Show Profile  Reply with Quote
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 - 11/24/2008 :  23:58:58  Show Profile  Visit hans.edt's Homepage  Reply with Quote
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 - 11/25/2008 :  00:02:58  Show Profile  Visit hans.edt's Homepage  Reply with Quote
Oops I forgot to include links to these products:
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000