View Message Thread (11 replies)
| xp_cmdshell copy from ftp site |
 |
From: Bob McClellan
Date Posted: 8/4/2009 9:13:00 AM
I am trying to automate a daily process.
I currently download an .xml file each day then
run a sp that shreds and processes it into one of our DBs.
I'd like to create a job to do this.
my thought was to use xp_cmdshell to bring down the file
but my EXEC statement errors out.
EXEC xp_cmdshell 'copy ftp://pathToFtpSite/Tuesday_08042009.xml
\\MyServerIp\Data\Downloads'
GO
the output returns
"ftp:" is not a recognized device.
The syntax of the command is incorrect.
Any suggestions on how to automate this process is much appreciated.
thanks in advance,
...bob
| Re: xp_cmdshell copy from ftp site |
|
From: Tibor Karaszi
Date Posted: 8/4/2009 9:39:00 AM
The OS command interpreter's COPY command do not know what an FTP site
is. Use some command-line FTP utility (should be plenty if you Google)
or perhaps the built-in FTP.EXE.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Bob McClellan" < bobmcc777@gmail.com> wrote in message
news:EBBC6E33-F3C0-4DA7-BE97-C3B0CB742FB9@microsoft.com...
>I am trying to automate a daily process.
> I currently download an .xml file each day then
> run a sp that shreds and processes it into one of our DBs.
>
> I'd like to create a job to do this.
> my thought was to use xp_cmdshell to bring down the file
> but my EXEC statement errors out.
>
> EXEC xp_cmdshell 'copy ftp://pathToFtpSite/Tuesday_08042009.xml
> \\MyServerIp\Data\Downloads'
> GO
>
> the output returns
> "ftp:" is not a recognized device.
> The syntax of the command is incorrect.
>
> Any suggestions on how to automate this process is much appreciated.
> thanks in advance,
> ..bob
| RE: xp_cmdshell copy from ftp site |
|
From: Linchi Shea
Date Posted: 8/4/2009 10:21:00 AM
And do not schedule to run the ftp exe via xp_cmdshell. Just add a step to
exxecute it as a straight OS job step.
Linchi
"Bob McClellan" wrote:
> I am trying to automate a daily process.
> I currently download an .xml file each day then
> run a sp that shreds and processes it into one of our DBs.
>
> I'd like to create a job to do this.
> my thought was to use xp_cmdshell to bring down the file
> but my EXEC statement errors out.
>
> EXEC xp_cmdshell 'copy ftp://pathToFtpSite/Tuesday_08042009.xml
> \\MyServerIp\Data\Downloads'
> GO
>
> the output returns
> "ftp:" is not a recognized device.
> The syntax of the command is incorrect.
>
> Any suggestions on how to automate this process is much appreciated.
> thanks in advance,
> ..bob
| Re: xp_cmdshell copy from ftp site |
|
From: Bob McClellan
Date Posted: 8/4/2009 11:44:00 AM
| Re: xp_cmdshell copy from ftp site |
|
From: Bob McClellan
Date Posted: 8/4/2009 11:46:00 AM
Thanks Linchi.
I will set it up as it's own OS job step.
"Linchi Shea" < LinchiShea@discussions.microsoft.com> wrote in message
news:7F303D92-D5DE-455F-A4B9-987DB1F2F42E@microsoft.com...
> And do not schedule to run the ftp exe via xp_cmdshell. Just add a step to
> exxecute it as a straight OS job step.
>
> Linchi
>
> "Bob McClellan" wrote:
>
>> I am trying to automate a daily process.
>> I currently download an .xml file each day then
>> run a sp that shreds and processes it into one of our DBs.
>>
>> I'd like to create a job to do this.
>> my thought was to use xp_cmdshell to bring down the file
>> but my EXEC statement errors out.
>>
>> EXEC xp_cmdshell 'copy ftp://pathToFtpSite/Tuesday_08042009.xml
>> \\MyServerIp\Data\Downloads'
>> GO
>>
>> the output returns
>> "ftp:" is not a recognized device.
>> The syntax of the command is incorrect.
>>
>> Any suggestions on how to automate this process is much appreciated.
>> thanks in advance,
>> ..bob
| Re: xp_cmdshell copy from ftp site |
|
From: Tibor Karaszi
Date Posted: 8/4/2009 12:00:00 PM
Good point, Linchi.
.... or perhaps use an SSIS package which has FTP built-in as a task.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Linchi Shea" < LinchiShea@discussions.microsoft.com> wrote in message
news:7F303D92-D5DE-455F-A4B9-987DB1F2F42E@microsoft.com...
> And do not schedule to run the ftp exe via xp_cmdshell. Just add a
> step to
> exxecute it as a straight OS job step.
>
> Linchi
>
> "Bob McClellan" wrote:
>
>> I am trying to automate a daily process.
>> I currently download an .xml file each day then
>> run a sp that shreds and processes it into one of our DBs.
>>
>> I'd like to create a job to do this.
>> my thought was to use xp_cmdshell to bring down the file
>> but my EXEC statement errors out.
>>
>> EXEC xp_cmdshell 'copy ftp://pathToFtpSite/Tuesday_08042009.xml
>> \\MyServerIp\Data\Downloads'
>> GO
>>
>> the output returns
>> "ftp:" is not a recognized device.
>> The syntax of the command is incorrect.
>>
>> Any suggestions on how to automate this process is much
>> appreciated.
>> thanks in advance,
>> ..bob
| Re: xp_cmdshell copy from ftp site |
|
From: Henrik Staun Poulsen
Date Posted: 8/5/2009 6:18:00 AM
Bob,
I need to upload files to a FTP server, and I've just created a small
upload.bat file, that I get xp_cmdshell to run, after it has created
the files.
upload.bat looks like this:
ftp -s:upload.dat ftp.myOwnSite.com
upload.dat looks like this:
userid
password
binary
put myfile.txt
quit
Line 1 is the UserID for the FTP site, Line 2 is the password for this
site. Then I use the Binary FTP command, to ensure that it is treated
correctly. Then I upload (put) the file I want and finally I tell it
to quit processing.
FTP.com is a part of Windows, and is installed on all PCs.
Best regards,
Henrik Staun Poulsen
www.stovi.com
On Aug 4, 5:10 pm, "Bob McClellan" < bobmcc...@gmail.com> wrote:
> I am trying to automate a daily process.
> I currently download an .xml file each day then
> run a sp that shreds and processes it into one of our DBs.
>
> I'd like to create a job to do this.
> my thought was to use xp_cmdshell to bring down the file
> but my EXEC statement errors out.
>
> EXEC xp_cmdshell 'copyftp://pathToFtpSite/Tuesday_08042009.xml
> \\MyServerIp\Data\Downloads'
> GO
>
> the output returns
> "ftp:" is not a recognized device.
> The syntax of the command is incorrect.
>
> Any suggestions on how to automate this process is much appreciated.
> thanks in advance,
> ..bob
| Re: xp_cmdshell copy from ftp site |
|
From: Bob
Date Posted: 8/5/2009 5:32:00 PM
Thanks for the reply and advice Henrik.
This looks like it should do exactly what I need.
I will play around with this. I just need to be able to tell it
dynamically each day what file to pull down. It is always a filename with
Account# + dayOfWeek + CurrentDaysDate.
My SP easily strings this together. I'm sure I can get the .bat file
to do the same.
Thanks again,
...bob
"Henrik Staun Poulsen" < hsp@stovi.com> wrote in message
news:ffeb38ce-e45d-44d7-88f6-1f0f1dc67609@k1g2000yqf.googlegroups.com...
Bob,
I need to upload files to a FTP server, and I've just created a small
upload.bat file, that I get xp_cmdshell to run, after it has created
the files.
upload.bat looks like this:
ftp -s:upload.dat ftp.myOwnSite.com
upload.dat looks like this:
userid
password
binary
put myfile.txt
quit
Line 1 is the UserID for the FTP site, Line 2 is the password for this
site. Then I use the Binary FTP command, to ensure that it is treated
correctly. Then I upload (put) the file I want and finally I tell it
to quit processing.
FTP.com is a part of Windows, and is installed on all PCs.
Best regards,
Henrik Staun Poulsen
www.stovi.com
On Aug 4, 5:10 pm, "Bob McClellan" < bobmcc...@gmail.com> wrote:
> I am trying to automate a daily process.
> I currently download an .xml file each day then
> run a sp that shreds and processes it into one of our DBs.
>
> I'd like to create a job to do this.
> my thought was to use xp_cmdshell to bring down the file
> but my EXEC statement errors out.
>
> EXEC xp_cmdshell 'copyftp://pathToFtpSite/Tuesday_08042009.xml
> \\MyServerIp\Data\Downloads'
> GO
>
> the output returns
> "ftp:" is not a recognized device.
> The syntax of the command is incorrect.
>
> Any suggestions on how to automate this process is much appreciated.
> thanks in advance,
> ..bob
| Re: xp_cmdshell copy from ftp site |
|
From: Bob McClellan
Date Posted: 8/7/2009 2:42:00 PM
Henrik,
I got the batch file working. Thanks again for this advice,
...bob
"Henrik Staun Poulsen" < hsp@stovi.com> wrote in message
news:ffeb38ce-e45d-44d7-88f6-1f0f1dc67609@k1g2000yqf.googlegroups.com...
Bob,
I need to upload files to a FTP server, and I've just created a small
upload.bat file, that I get xp_cmdshell to run, after it has created
the files.
upload.bat looks like this:
ftp -s:upload.dat ftp.myOwnSite.com
upload.dat looks like this:
userid
password
binary
put myfile.txt
quit
Line 1 is the UserID for the FTP site, Line 2 is the password for this
site. Then I use the Binary FTP command, to ensure that it is treated
correctly. Then I upload (put) the file I want and finally I tell it
to quit processing.
FTP.com is a part of Windows, and is installed on all PCs.
Best regards,
Henrik Staun Poulsen
www.stovi.com
On Aug 4, 5:10 pm, "Bob McClellan" < bobmcc...@gmail.com> wrote:
> I am trying to automate a daily process.
> I currently download an .xml file each day then
> run a sp that shreds and processes it into one of our DBs.
>
> I'd like to create a job to do this.
> my thought was to use xp_cmdshell to bring down the file
> but my EXEC statement errors out.
>
> EXEC xp_cmdshell 'copyftp://pathToFtpSite/Tuesday_08042009.xml
> \\MyServerIp\Data\Downloads'
> GO
>
> the output returns
> "ftp:" is not a recognized device.
> The syntax of the command is incorrect.
>
> Any suggestions on how to automate this process is much appreciated.
> thanks in advance,
> ..bob
| Re: xp_cmdshell copy from ftp site |
|
From: Henrik Staun Poulsen
Date Posted: 8/10/2009 2:33:00 AM
Hi Bob,
I'm glad it worked.
Best regards,
Henrik Staun Poulsen
www.stovi.com
On Aug 7, 10:41 pm, "Bob McClellan" < bobmcc...@gmail.com> wrote:
> Henrik,
> I got the batch file working. Thanks again for this advice,
> ..bob
>
> "Henrik Staun Poulsen" < h...@stovi.com> wrote in messagenews:ffeb38ce-e45d-44d7-88f6-1f0f1dc67609@k1g2000yqf.googlegroups.com...
> Bob,
>
> I need to upload files to a FTP server, and I've just created a small
> upload.bat file, that I get xp_cmdshell to run, after it has created
> the files.
>
> upload.bat looks like this:
>
> ftp -s:upload.dat ftp.myOwnSite.com
>
> upload.dat looks like this:
>
> userid
> password
> binary
> put myfile.txt
> quit
>
> Line 1 is the UserID for the FTP site, Line 2 is the password for this
> site. Then I use the Binary FTP command, to ensure that it is treated
> correctly. Then I upload (put) the file I want and finally I tell it
> to quit processing.
>
> FTP.com is a part of Windows, and is installed on all PCs.
>
> Best regards,
> Henrik Staun Poulsenwww.stovi.com
>
> On Aug 4, 5:10 pm, "Bob McClellan" < bobmcc...@gmail.com> wrote:
>
>
>
> > I am trying to automate a daily process.
> > I currently download an .xml file each day then
> > run a sp that shreds and processes it into one of our DBs.
>
> > I'd like to create a job to do this.
> > my thought was to use xp_cmdshell to bring down the file
> > but my EXEC statement errors out.
>
> > EXEC xp_cmdshell 'copyftp://pathToFtpSite/Tuesday_08042009.xml
> > \\MyServerIp\Data\Downloads'
> > GO
>
> > the output returns
> > "ftp:" is not a recognized device.
> > The syntax of the command is incorrect.
>
> > Any suggestions on how to automate this process is much appreciated.
> > thanks in advance,
> > ..bob- Hide quoted text -
>
> - Show quoted text -
Would you like to track this thread?
By adding this News Thread to your Favorites Area you can refer to it later with just a click of the mouse.
Also you can optionally be notified instantly whenever there are any replies
posted to this Thread. To add this Thread to your Favorites Area just click on the red arrow next to the subject of the thread above
 .
|
