Search code examples
sql-serverxmlstored-proceduresexportsqlcmd

How to export more than 1MB in XML format using sqlcmd and without an input file?

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

Solution

  • 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