| Author |
 Topic  |
|
AskSQLTeam
Ask SQLTeam Question
USA
0 Posts |
|
|
MuffinMan
Posting Yak Master
USA
107 Posts |
|
|
izaltsman
A custom title
USA
1139 Posts |
 Posted - 10/01/2001 : 14:50:30
|
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 |
 |
|
|
p2bl
Yak Posting Veteran
China
54 Posts |
Posted - 10/03/2001 : 10:17:44
|
I think if u have to complete a job like this,why not using extended procedure
|
|
|
|
mfemenel
Professor Frink
USA
1414 Posts |
Posted - 10/03/2001 : 10:26:33
|
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. "
|
|
|
|
GreatInca
Posting Yak Master
USA
102 Posts |
Posted - 11/06/2001 : 15:43:19
|
XPs execute faster though. Anybody know where to get an FTP extended procedure?
DTS has FTP too but the filenames have to be fixed.
|
|
|
|
robvolk
Most Valuable Yak
USA
15559 Posts |
Posted - 11/06/2001 : 15:56:32
|
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. 
|
|
|
|
rrb
SQLTeam Poet Laureate
Australia
1478 Posts |
|
|
izaltsman
A custom title
USA
1139 Posts |
Posted - 02/27/2002 : 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...
|
|
|
|
rrb
SQLTeam Poet Laureate
Australia
1478 Posts |
Posted - 02/27/2002 : 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" |
|
|
|
axisperfect
Starting Member
21 Posts |
Posted - 02/02/2005 : 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? |
|
|
|
axisperfect
Starting Member
21 Posts |
Posted - 02/03/2005 : 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
|
|
|
|
ws5926
Yak Posting Veteran
USA
88 Posts |
Posted - 11/10/2005 : 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 |
|
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
Posted - 11/11/2005 : 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 |
|
|
|
ws5926
Yak Posting Veteran
USA
88 Posts |
Posted - 11/11/2005 : 07:53:28
|
I don't want to cmdshell. That was the whole point of writing this dll.
Live to Throw
Throw to Live |
|
|
|
robvolk
Most Valuable Yak
USA
15559 Posts |
Posted - 11/11/2005 : 19:53:05
|
| The link in the original article has a PUT example. |
|
|
|
jstrydom
Starting Member
1 Posts |
Posted - 11/16/2007 : 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 ? |
|
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
Posted - 11/19/2007 : 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 |
|
|
|
ccastillo
Starting Member
1 Posts |
Posted - 01/22/2008 : 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?
|
|
|
|
hans.edt
Starting Member
2 Posts |
Posted - 11/24/2008 : 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. |
|
|
|
hans.edt
Starting Member
2 Posts |
Posted - 11/25/2008 : 00:02:58
|
Oops I forgot to include links to these products:
|
|
|
|
Topic |
|
Article: Using FTP in Transact-SQL
