ISQL is truncating contents of text field sent to text file.

Under SQL 2000 I have a table that consists of a single column of text
type, each record holds about 15-25,000 chars. This data happens to be
an xml, and I want to write this records to a text file. In order to
accomplish this i'm using ISQL.exe, but i'm finding that some of the
text type records aren't being written complete (truncated at 8k
chars).

I have tried increasing the width of columns (-w), and the maximum
text size (-x) {the maximum length of text data to return} parameters
for executing ISQL with no luck. High values make it crash.

Problems:
* If the data is longer than 8k, the text is truncated at 8k chars.

* If the data is longer than the value given for width of columns (-
w), an xml element might be separated in two when reaching the end of
line, making the xml invalid.
instead of :
.......
i'm getting this if the element reaches the endofline
.... ement="2">...

Is there a way to write to a plain-text file the full contents of a
text field without having its contents being truncated? If not, a
workaround would be to output the text field in small chunks, any
guide to handle substrings and locate chars inside a text field?, will
be needing to seek and output complete elements "<... .../>"

Regards
Elias Willard / Rodrigo Perez

Re: ISQL is truncating contents of text field sent to text file.

Rod (rod.perez@gmail.com) writes:
> Under SQL 2000 I have a table that consists of a single column of text
> type, each record holds about 15-25,000 chars. This data happens to be
> an xml, and I want to write this records to a text file. In order to
> accomplish this i'm using ISQL.exe, but i'm finding that some of the
> text type records aren't being written complete (truncated at 8k
> chars).

Any particular reason you are using ISQL? You may have better luck with
OSQL. Or maybe even better luck with BCP.




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

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

Re: ISQL is truncating contents of text field sent to text file.

> Any particular reason you are using ISQL? You may have better luck with
> OSQL. Or maybe even better luck with BCP.
>

OSQL (as ISQL) breaks the output into lines of fixed length, this
brings some
issues as we are handling xml data (invalid elements are reported when
they reach
the end-of-line) .

BCP seems the way to go as it's not truncating the output and writes
without a prob
upto 100k chars per line (tested). Now, something that surfaced after
some testing to
implement BCP usage, is that BCP only writes to text file the very
first "select" statement
of the several that conform my stored procedure, will need to find a
workaround.

Thanks

Re: ISQL is truncating contents of text field sent to text file.

Rod (rod.perez@gmail.com) writes:
> BCP seems the way to go as it's not truncating the output and writes
> without a prob upto 100k chars per line (tested). Now, something that
> surfaced after some testing to implement BCP usage, is that BCP only
> writes to text file the very first "select" statement of the several
> that conform my stored procedure, will need to find a workaround.

In your original post you only talk about a table with the data type
text. Suddently there are stored procedures. Do these procedures use
FOR XML? Or do they select from text column that just happens to hold
XML.


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

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

Re: ISQL is truncating contents of text field sent to text file.

On 9 jul, 16:39, Erland Sommarskog wrote:
> Rod (rod.pe...@gmail.com) writes:
> > BCP seems the way to go as it's not truncating the output and writes
> > without a prob upto 100k chars per line (tested). Now, something that
> > surfaced after some testing to implement BCP usage, is that BCP only
> > writes to text file the very first "select" statement of the several
> > that conform my stored procedure, will need to find a workaround.
>
> In your original post you only talk about a table with the data type
> text. Suddently there are stored procedures. Do these procedures use
> FOR XML? Or do they select from text column that just happens to hold
> XML.
>

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.

Re: ISQL is truncating contents of text field sent to text file.

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/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx