We help IT Professionals succeed at work.

bcp export via master..xp_cmdshell

exec master..xp_cmdshell 'bcp "SELECT * FROM testdatabase..test FOR XML RAW('test'), XMLSCHEMA('URN:test.COM')"
queryout \\webtest\Webfolder\test.xml -SServer01 -Uuser -Pxxx -c -t'

i am getting
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'test'.

I cannot see where i have got this syntax wrong


Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'myview'.
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string ')"
queryout \\testserver\internet\myvew.xml  -Smyserver -Uuser -Pxxxx -c -t
 -c -t
'.
Comment
Watch Question

Expert of the Quarter 2010
Expert of the Year 2010
Commented:
exec master..xp_cmdshell 'bcp "SELECT * FROM testdatabase..test FOR XML RAW(''test''), XMLSCHEMA(''URN:test.COM'')"
queryout \\webtest\Webfolder\test.xml -SServer01 -Uuser -Pxxx -c -t'

double up your single quotes
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
Also, in case you actually have the string in 2 lines, make sure it is a single unbroken line.

exec master..xp_cmdshell 'bcp "SELECT * FROM testdatabase..test FOR XML RAW(''test''), XMLSCHEMA(''URN:test.COM'')" queryout \\webtest\Webfolder\test.xml -S Server01 -U user -P xxx -c -t'

-S, -U and -P all require a space before the parameters
Amanda WalshawData Engineer / Business Analyst

Author

Commented:
exec master..xp_cmdshell 'bcp "SELECT * FROM testdatabase..test FOR XML RAW(''test''), XMLSCHEMA(''URN:test.COM'')"
queryout \\webtest\Webfolder\test.xml -SServer01 -Uuser -Pxxx -c -t'


have now added the double quotes but all i get is the output for bcp usage see attached and script I am using below

exec master..xp_cmdshell 'bcp "SELECT * FROM testdatabase..test FOR XML RAW(''test''), XMLSCHEMA(''URN:test.COM'')"
queryout \\webtest\Webfolder\test.xml -SServer01 -Uuser -Pxxx -c -t'


output.csv
Amanda WalshawData Engineer / Business Analyst

Author

Commented:
have inclued the space re above does not show, but still get the output file


exec master..xp_cmdshell 'bcp "SELECT * FROM testdatabase..test FOR XML RAW(''test''), XMLSCHEMA(''URN:test.COM'')"
queryout \\webtest\Webfolder\test.xml -S Server01 -U user -P xxx -c -t'
Try this:

exec master..xp_cmdshell 'bcp "SELECT * FROM testdatabase.dbo.test FOR XML RAW(''test''), XMLSCHEMA(''URN:test.COM'')"
queryout \\webtest\Webfolder\test.xml -S Server01 -U user -P xxx -c -t'
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
Hi,

Have you forgotten 3rd part, which is NOT to break it into two lines.  BCP will only see up to before "queryout" if you split the lines (from 'bcp onwards).

exec master..xp_cmdshell
'bcp "SELECT * FROM testdatabase..test FOR XML RAW(''test''), XMLSCHEMA(''URN:test.COM'')" queryout \\webtest\Webfolder\test.xml -S Server01 -U user -P xxx -c -t'