Using xp_cmdshell to BCP to ascii file

[cbeggan]

Hi

I'm trying to export data from a stored procedure. I am attempting to call an SP to do my select and throw the result set to a DOS ascii file.

Now, my bcp command is built dynamically looks like this:

bcp &quot;EXEC server.db.dbo.sp_cms_output_client_data&quot; queryout &quot;C:\JUNK.txt&quot; -c -r \n -t -U<USER> -P<PASSWORD> -S<SERVER> -m0 -E >> C:\JUNK.log -oC:\JUNK.out

Problem is: No file output whatsoever, despite getting the DOS info back into the grid:

Starting copy...
NULL
2 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total        1
NULL

I have SELECTed the above command string and pasted into a DOS box and it works fine - producing output and error files. But I cannot see why it will not work from xp_cmdshell.

Incidentally

exec master..xp_cmdshell 'dir > junk.txt'

produces no junk.txt file either!

I guess its a write permissions thing ?? Anyone got any ideas?

Also I'm executing master..xp_cmdshell from the sa because it will not execute from the intended user account. How do I change the privs for execution - as xps are not listed in the Permissions Tab in the User Properties window...?? ------
Dublin, Ireland.

 

[tlbroadbent]

Are you executing this BCP statement from a stored procedure? Where are you looking for the output - on the local PC or on the server? I recommend looking on the server if you execute this from a stored procedure. The same thing for the output of xp_cmdshell. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[cbeggan]

[Lightbulb flickers on]

Thanks! This is what happens towards the end of 14 hour days!

Was looking on local drives, will now check on the server! ------
Dublin, Ireland.