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 tools to make flat backups

Does anyone know if there is a way to run a command at the DOS prompt
to make a complete flat file (.bak) backup of a database? I know you
can do this using the enterprise manager by right clicking on the
db..., but I am curious to find out if I can add a command into my
batch scripts as part of my code migration procedures?
Thanks!
-Mike
You could use osql e.g.
osql -Q "BACKUP DATABASE pubs TO DISK = 'e:\backups\pubs.bak'" -E
Ray Mond
|||Yes, Ray, Perfecto!! That's what I was looking for. Thanks for the
help...
Happy Holidays!!