0
Reply

how to use bcp command insise scalar variable

sharmila k

sharmila k

Oct 24 2013 2:49 AM
4.6k

Dear Friend,

As I want to save and display xml scalar variable result in bcp command in sql server.

Here I mention my code,

declare @s xml

set @s=(select '',

(select Enquiry_id

from test.dbo.tbl_receipt for xml path('a'),type)

for xml path('s'),type)

--select @s

EXEC xp_cmdshell 'bcp "''+select @s+''" queryout c:\a\aa.xml -S WebDeveloper -c -U saranya -P saranya -T'

 

If I run the above code it shows the following output. I don't know how to solve this error.

Note:

I have some sql database value in sql table .I tried to convert sql table value to xml file format.

And I try to save that xml format into scalar variable such as (declare @x xml). Now I want to save that scalar variable result to use in bcp command and save on my server (root directory).

Please anyone help me. It's urgent.

Output

SQLState = 37000, NativeError = 102

Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near '+select @s+'.

SQLState = 37000, NativeError = 8180

Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement(s) could not be prepared.

NULL

Please any one helps me. It's very urgent. I tried but I can't the clear solution.