how to copy files using xp_cmdshell

Howdy, hope someone can help me out with this.
I want to run a job each night that copies files from one server to another.
I cant even get a simple copy one file from one directory to another,
statement to work.
When I try :

@cmd 'copy c:\temp\file1.txt c:\backups\file1.txt'
master.dbo.xp_cmdshell @cmd

I get the msg: 'c' is not recognised as an internal command, program or
batch file, Null.

What I ideally want to do is pass xp_cmdshell variables for the 2 file's
path+names. I have tried numerous variations of single and double quotes
without success, which is why I decided to work up form the simple 'copy
c:\temp\file1.txt c:\backups\file1.txt', but can't even get that to work.
Any advice much appreciated.

Re: how to copy files using xp_cmdshell

SJM (na) writes:
> Howdy, hope someone can help me out with this. I want to run a job each
> night that copies files from one server to another. I cant even get a
> simple copy one file from one directory to another, statement to work.
> When I try :
>
> @cmd 'copy c:\temp\file1.txt c:\backups\file1.txt'
> master.dbo.xp_cmdshell @cmd
>
> I get the msg: 'c' is not recognised as an internal command, program or
> batch file, Null.
>
> What I ideally want to do is pass xp_cmdshell variables for the 2 file's
> path+names. I have tried numerous variations of single and double quotes
> without success, which is why I decided to work up form the simple 'copy
> c:\temp\file1.txt c:\backups\file1.txt', but can't even get that to work.
> Any advice much appreciated.

I would guess you have made a mistake in the declaration of @cmd:

DECLARE @cmd varchar

this is the same as

DECLARE @cmd varchar(1)



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: how to copy files using xp_cmdshell

On Nov 18, 3:15 pm, Erland Sommarskog wrote:
> SJM (na) writes:
> > Howdy, hope someone can help me out with this. I want to run a job each
> > night that copies files from one server to another. I cant even get a
> > simple copy one file from one directory to another, statement to work.
> > When I try :
>
> > @cmd 'copy c:\temp\file1.txt c:\backups\file1.txt'
> > master.dbo.xp_cmdshell @cmd
>
> > I get the msg: 'c' is not recognised as an internal command, program or
> > batch file, Null.
>
> > What I ideally want to do is pass xp_cmdshell variables for the 2 file's
> > path+names. I have tried numerous variations of single and double quotes
> > without success, which is why I decided to work up form the simple 'copy
> > c:\temp\file1.txt c:\backups\file1.txt', but can't even get that to work.
> > Any advice much appreciated.
>
> I would guess you have made a mistake in the declaration of @cmd:
>
> DECLARE @cmd varchar
>
> this is the same as
>
> DECLARE @cmd varchar(1)
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/down loads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/boo ks.mspx- Hide quoted text -
>
> - Show quoted text -

IMO, it should throw error saying "Length is not specified"
Sometimes it becomes tedius to debug if you forget to specify column
width

Re: how to copy files using xp_cmdshell

Madhivanan (madhivanan2001@gmail.com) writes:
> IMO, it should throw error saying "Length is not specified"
> Sometimes it becomes tedius to debug if you forget to specify column
> width

Yes, the default of 1 is silly.

While not explicitly mentioned, this is typically something that should
be flagged when SET STRICT_CHECKS ON is in effect, a SET option which I
have suggested in
https://connect.microsoft.com/SQLServer/feedback/ViewFeedbac k.aspx?FeedbackID=260762.

Votes are welcome!



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx