Command works in DOS but not in xp_cmdshell

Can anyone give clues as to why the same copy command works in
DOS and not in xp_command shell.I am an admin in both servers and
in the database.

The results indicate one file is copied but no file is found
at destination.

Your input is appreciated

Vincento

copy "D:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\QMISDEV\back1.
txt" "\\corp156\d$\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\QMISDEV\"

works just fine but

use
master
exec xp_cmdshell 'copy "D:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\QMISDEV\back1.
txt" "\\corp156\d$\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\QMISDEV\"'What is the user context that the script is running in? Usually it is
system (I think), which isn't going to have access rights to corp156.

Zach

Vincento Harris wrote:
> Can anyone give clues as to why the same copy command works in
> DOS and not in xp_command shell.I am an admin in both servers and
> in the database.
> The results indicate one file is copied but no file is found
> at destination.
> Your input is appreciated
> Vincento
>
>
> copy "D:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\QMISDEV\back1.
> txt" "\\corp156\d$\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\QMISDEV\"
> works just fine but
> use
> master
> exec xp_cmdshell 'copy "D:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\QMISDEV\back1.
> txt" "\\corp156\d$\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\QMISDEV\"'|||"Vincento Harris" <wumutek@.yahoo.com> wrote in message
news:2fa13ee7.0410130938.3a4db7ec@.posting.google.c om...
> Can anyone give clues as to why the same copy command works in
> DOS and not in xp_command shell.I am an admin in both servers and
> in the database.
> The results indicate one file is copied but no file is found
> at destination.
> Your input is appreciated
> Vincento
>
>
> copy "D:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\QMISDEV\back1.
> txt" "\\corp156\d$\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\QMISDEV\"
> works just fine but
> use
> master
> exec xp_cmdshell 'copy "D:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\QMISDEV\back1.
> txt" "\\corp156\d$\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\QMISDEV\"'

I tried this out, and it worked fine for me (I didn't test using a share,
but I did test using folder names with spaces) - was the result you saw "1
file(s) copied"? Or did you run this as a scheduled job perhaps? When a
sysadmin executes xp_cmdshell, it runs as the MSSQL service account, so you
might want to check that account's permissions also.

Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message news:<416d9609$1_3@.news.bluewin.ch>...
> "Vincento Harris" <wumutek@.yahoo.com> wrote in message
> news:2fa13ee7.0410130938.3a4db7ec@.posting.google.c om...
> > Can anyone give clues as to why the same copy command works in
> > DOS and not in xp_command shell.I am an admin in both servers and
> > in the database.
> > The results indicate one file is copied but no file is found
> > at destination.
> > Your input is appreciated
> > Vincento
> > copy "D:\Program Files\Microsoft SQL
> > Server\MSSQL\BACKUP\QMISDEV\back1.
> > txt" "\\corp156\d$\Program Files\Microsoft SQL
> > Server\MSSQL\BACKUP\QMISDEV\"
> > works just fine but
> > use
> > master
> > exec xp_cmdshell 'copy "D:\Program Files\Microsoft SQL
> > Server\MSSQL\BACKUP\QMISDEV\back1.
> > txt" "\\corp156\d$\Program Files\Microsoft SQL
> > Server\MSSQL\BACKUP\QMISDEV\"'
> I tried this out, and it worked fine for me (I didn't test using a share,
> but I did test using folder names with spaces) - was the result you saw "1
> file(s) copied"? Or did you run this as a scheduled job perhaps? When a
> sysadmin executes xp_cmdshell, it runs as the MSSQL service account, so you
> might want to check that account's permissions also.
> Simon
I appreciate your answers guys .It does work on the same server for me too.
When the mssqlserver was under local admin account
The result was " Access denied"

When I made this account part of the admin account
The result was "1 file copied" and it actually works

So my problem was not making mssqlservice account an admin
of the //server (//corp156)

Thanks again for the exceptional contributions I always get from
this group

Vincento

command line and insert

I'm trying to insert into a table 2 values one of which is an

exec master..xp_cmdshell @.command where I have assigned @.command with a value

this statement gives me the result into a 1 col. table fine:

insert into mytable99(col1) exec master..xp_cmdshell @.command

now what I want to do is put col2 in there as well!!

ie. insert into mytable99(col1,col2) values (exec master..xp_cmdshell @.command, '123')

I get a sytax error ... on the exec ??

Could anyone help re the proper way of doing this ... thanks in advancedid'nt know U could do something like this - sounds possibly clever

Syntax wise this command expects the optional parameter no_output after the comma

xp_cmdshell {'command_string'} [, no_output]

Your probably better off with with assigning each value to a local variable then inserting the contents of the variables into the table - something like

SET @.CmdShellOutput = exec master..xp_cmdshell @.command
SET @.StringNumber = '123'

insert into mytable99(col1,col2)
values (@.CmdShellOutput , @.StringNumber)

Sorry not had time to test this syntax but hopefully U get the picture.

GW