Microsoft KB Archive/275583

= PRB: SELECT...FOR XML AUTO Results Are Separated into Lines of 2033 Chars with ODBC =

Article ID: 275583

Article Last Modified on 10/16/2003

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q275583





SYMPTOMS
When you use Query Analyzer (or any other ODBC application) to retrieve data using the FOR XML AUTO clause, only 2033 characters are returned per row. The application's maximum output width setting cannot be used to make the rows longer.



CAUSE
ODBC, which is used by Query Analyzer, does not handle the FOR XML clause. It returns the data in 4K rows which, allowing for overhead and double-byte character set (DBCS), comes to 2033 characters.



WORKAROUND
This is a known limitation with ODBC. Because Query Analyzer uses ODBC, there is no workaround for this problem when using Query Analyzer. For any custom applications, you can use any of the following methods instead:
 * Use OLE DB instead of ODBC. OLE DB supports the FOR XML clause and will append the data together. For this to occur, use adExecuteStream for implementing ADO on top of OLE DB, or use Query Interface's ISequentialStream if using OLE DB directly.
 * Use XSLT to handle the formatting.
 * Code your ODBC application to append the rows together as necessary.



Steps to Reproduce Behavior
 Start Query Analyzer. On the Tools menu, click Options. Click the Results tab and set Maximum characters per column to 8192 (the maximum value allowed). This step verifies that a string of 5000 characters can be shown in Query Analyzer. Type CTRL+T to select the Results in Text output option.  Run the following query: SELECT REPLICATE('x', 5000) The result is one line 5000 characters long.   Run the following query: SELECT * FROM pubs..authors FOR XML AUTO Formatted results would be expected to have the following properties: <ul> All rows from the table are returned in XML notation.</li> The row length of each row is equal to or lower than the maximum value specified for the Maximum characters per column option.</li> The number of rows is: ( (length_of_XML_string / max_chars_per_col) + 1 )</li></ul>

However, the above example, which returns data from 23 rows, is shown in Query Analyzer as 2 rows of data. The first row ends after 2033 characters. No data is lost, but the format is not very readable to a human eye. </li></ol>

Additional query words: oledb, sql server, ole db, odbc, mdac

Keywords: kbprb kbpending KB275583

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.