1

In SQL Server 2008, I want to export the result of a stored procedure to a file using sqlcmd utility. Now the end of my stored procedure is a select statement with a "for xml path.." clause at the end.

I read on BOL that if I don't want my output truncated when reaching 1MB file size, I have to use this :XML ON command, but it should be placed on its own line, before calling the stored procedure.

  • Does any of you experts know if it is possible to do that without specifying an input file for sqlcmd? (I'm calling sqlcmd like this:

exec master..xp_cmdshell 'sqlcmd -Q"exec storedProcedureName @param1=value1, @param2=value2" -o c:\exportResults.xml -h-1 -E',

but "storedProcedureName" and its parameters can change, which would mean 1 input file per passed parameters to sqlcmd)

  • Also, it seems that I can't use bcp instead of sqlcmd because my stored procedure is creating a temporary table and performing DML statements on it?

Thanks a lot

jon
  • 408
  • 6
  • 20

2 Answers2

0

I answer my question (using powershell):

http://ask.sqlservercentral.com/questions/5322/is-it-possible-to-use-the-xml-on-option-without-using-an-input-file/5380#5380

jon
  • 408
  • 6
  • 20
  • [here it is](http://ask.sqlservercentral.com/questions/6454/is-it-possible-to-use-the-xml-on-option-without-us.html) – jon Apr 14 '12 at 15:59
0

If you can use powershell as your executing shell, then you can have the :XML ON in the -Q parameter as well, just encode endline character \\n (`n in powershell)

sqlcmd -Q":XML ON\`n exec storedProcedureName @param1=value1, @param2=value2" -o c:\exportResults.xml -h-1 -E
Pasi Savolainen
  • 2,460
  • 1
  • 22
  • 35