Problem sending FTP
-
Hi I really need help on this,
This is the scenario, I want to send a file from my sql server using FTP to a remote location. I have prepared a .cmd file with the ftp command using -s modifier to get the ftp instructions to connect from a text file. when executed from a local console on the server everything goes smooth. To automate the task I have created a job to run the file using an OS command step, but when I start the job, I get "command not valid" and "disconnected" messages in the response file and can't send the file.
Is there a difference in the way OS commands are executed from a local console and when invoked through SQL server jobs? Is there any way I can execute the ftp command properly using jobs.
I'd really appreciate your help, Thanx in advance.
Raúl P.
-
Here's a good article on using FTP in TSQL http://www.sqlteam.com/item.asp?ItemID=6002. Once you create the stored proc you can set the proc to run as a job. Hope this helps you out
-
Keep in mind that when you run this interactively, it uses your credentials to execute, but when you schedule the task as a job, the SQL Agent's credentials are used. Make sure that this account has all the necessary privileges to the directories that you're calling the ftp script from and trying to ftp to.
HTH -- Cheers
-
Thanx veteran, but I have no problem with permissions, already checked that.
Raul.
-
The way that I accomplish this task is to create a DTS, add an "Execute Process Task" and call a batch file that calls the FTP script. So the Win32 process (in the Exec Process Task) is...
//servername/path/batchfilename.bat (using UNC notation)
where
batchfilename.bat contains the line...
FTP -s://servername/path/scriptfilename.txt
where
scriptfilename.txt contains the ftp script commands to be executed.
I then schedule the DTS as a job. Works beautifully.
Cheers
-
Already did that, the problem is when done that way the ftp proccess runs in the machine that's invoking the DTS package. I run the DTS package from a VB application running on workstations in remote offices most of the times. Due to access restrictions FTP is blocked in remote offices, that's why I have to make sure the ftp proccess runs in the server.
Using jobs or xp_cmdshell was the approach but neither worked fine, that is: I still get the "Command not valid" messages in the ftp prompt response file.
Raul.
-
Schedule the DTS as a Job and then it will run on the server under the SQL Agent accounts credentials.
If need be, you can start the JOB remotely.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply