Hello,
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