January 8, 2010 at 9:33 am
I’m using xp_cmdshell to do FTP. When I enter wrong user name or pass word in FTP command, the xp_cmdshell still return a value ‘0’, which states success. The xp_cmdshell output shows 'Connection closed by remote host.'
My question is, should xp_cmdshell return a negative value when the connection didn’t made? Because the return value is ‘0’ my error handler can’t capture this error so my program runs successfully but the file didn’t get FTPd….
What should I do in my program to capture this error?
Mary Ming Wang
January 8, 2010 at 9:39 am
The problem is that ftp opens its own shell. It allows you to re-enter the password (or user), when the connection fails.
You might look at using the SSIS tasks instead to move files through FTP.
January 8, 2010 at 9:48 am
Thank you for the suggestions.
Because the ftp open its own shell, the xp_cmdshell will return ‘0’ if the remote server busy or not available at file transmission time, are those correct?
Mary Ming Wang
January 8, 2010 at 9:54 am
I'm not 100% sure of all the places where ftp returns 1, but I think a "quit" or a "remote closed" will not force an error. I think only of the ftp crashes will it return 1, so most things will return 0.
I would look for something more programmable. I've used an input file with ftp as you're doing, but it's not reliable. A control, even something that's scriptable with VB Script, or the SSIS task would work better.
Good luck
January 8, 2010 at 10:00 am
I'll keep in mind.
Thanks.
Mary Ming Wang
January 8, 2010 at 11:49 am
I found an instant quick fix for my problem; I write the xp_cmdshell output into a table, than check the table to see if there are any ‘Not connected’ or ‘File not found’ string. If yes, force program returned -1 so that my error handler can capture it.
Mary Ming Wang
January 8, 2010 at 11:50 am
Excellent, thanks for the update and glad something is working for you.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply