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


The information in this article applies to:

  • Microsoft SQL Server 7.0
  • Microsoft SQL Server 2000 (all editions)
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.

MORE INFORMATION

Steps to Reproduce Behavior

  1. Start Query Analyzer.
  2. 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.
  3. Type CTRL+T to select the Results in Text output option.
  4. Run the following query: 
    SELECT REPLICATE('x', 5000)
    The result is one line 5000 characters long.
  5. Run the following query: 
    SELECT * FROM pubs..authors FOR XML AUTO
    Formatted results would be expected to have the following properties:
    • All rows from the table are returned in XML notation.
    • The row length of each row is equal to or lower than the maximum value specified for the Maximum characters per column option.
    • The number of rows is: ( (length_of_XML_string / max_chars_per_col) + 1 )
    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.
Modification Type:MajorLast Reviewed:10/16/2003
Keywords:kbpending kbprb KB275583
©2003 Microsoft Corporation. All rights reserved.