xp_cmshell

I am trying to execute a query through xp_cmdshell. when i run it through command prompt, it works fine.
but when i run the same through xp_cmdshell, it says 0 rows affected.
i have been able to use xp_cmdshell successfully before.
here are the 2 statements. i am running on sql server 7.0, sp 3.


bcp "select * from pubs.dbo.authors" queryout c:\Test\test2.txt -c -q -Sservername -U"username" -P"123"

exec xp_cmdshell 'bcp "select * from pubs.dbo.authors" queryout "c:\\Test\test2.txt" -c -q -Sservername -U"username" -P"123"'

thanks!

Ads,
It may be a permissions issue. Check out xp_cmdshell in BOL for details of what security context this command runs under.

Jeff

The user is member of sysadmin group! thats what BOL says!

Is the c:\Test\ directory that you are targeting on the SQL Server itself?

xp_cmdshell targets itself even if you are running the proc from a remote server. So, if the SQL Server doesn't have c:\Test, then it will fail. The UNC path to the directory will work though.

It must be a typo though
*
queryout "c:\\Test\test2.txt"

As Rawhide said, When you run from xp_cmdshell the file will be created in the server but if you run from your desktop cmd prompt, it will created locally.

Quote:

---

Originally posted by skhanal
It must be a typo though
*
queryout "c:\\Test\test2.txt"

---

Yes, but it should still be parsed correctly.