xp_cmdshell not working with FTP on server

Question

xp_cmdshell not working with FTP on server

starflyer

SSC Eights!

Points: 979
More actions
February 2, 2007 at 12:42 pm

#94942

I have a problem where I am simply trying to execute a SQL Server job (for now I am using Query Analyzer to troubleshoot), but here is what I am executing:
xp_cmdshell 'ftp.exe -s:E:\Data\ftp_def.txt 22.222.22.22'
the ftp_def contains the following
username
password123
binary
MPUT E:\Data\testfile.mdb
y
BYE
Now for my environment:
Windows Server 2003 Standard Edition SP 1
SQL Server 2000 standard edition (SP4) .2039
I am running the MSSQLServer service as local system (not my choice)
Running the SQLServerAgent Service as a domain account that has SA on SQL Server and local administrator to the server.
Also, I can run the FTP command at a command prompt and it works great.
This setup works on my local workstation when I developed it. Then I transfer it to the server and I get the following errors from running the xp_cmdshell:
Invalid command.
Invalid command.
Not connected.
Not connected.
Invalid command.
user
password1234
binary
MPUT E:\Data\testfile.mdb
y
BYE
> ftp: connect :Connection refused
NULL
Any help would be appreciated.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

Where is the "E" drive... on the server or on your local drive? If it's not on the server, you must use the UNC name back to the path where the FTP control and MDB files are actually located AND the SQL Server SERVICE must be logged in as a user that can "see" it.

Try this...

EXEC Master.dbo.xp_CmdShell 'Dir E:\Data\*.*'

... if it doesn't work, then that's the problem.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

starflyer SSC Eights! Points: 979 More actions · Answer 2

The E: drive is local....but the following has happened

We set up an FTP share on another server before any security changes are made. The process works against that server. Security between one server to another is the problem.