Posted by Erland Sommarskog on 07/10/07 21:40

Rod (rod.perez@gmail.com) writes:
> The text data is being generated on-the-fly using FOR XML by running
> several Select statements in series (some headers and elements are
> also sent to output). All these was compiled into a Stored Procedure
> for modularity.
>
> At first we were inserting each FOR XML to a table (SQL2k5)
> only to find the solution won't run under SQL 2000, so we rolled back
> to find a single solution compatible with SQL Server 2k & 2k5.
>
> After asking on usenet, and being suggested to use BCP, we now
> get a text-file including the output (w/o truncation) from the very
> first Select statement inside the SP while the other Select statements
> are ignored or not included in the text-file generated by BCP.

I would suggest that you are best off writing a client program that
receives the XML documents and writes them to file.

The alternative would be have one procedure per query and the have have a
command file that runs BCP once for each procedure, and at the end
concatenates the files into one with COPY. But I am a little wary
that BCP + FOR XML may not be something when always works. When first
asked, you only talked about a table column, why BCP appear to be a good
solution.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]