You are currently offline, waiting for your internet to reconnect

The XML data row is truncated at 2,033 characters when you use the SqlDataReader object

This article was previously published under Q310378
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
This article refers to the following Microsoft .NET Framework Class Library namespace:
  • System.Data.SqlClient
SYMPTOMS
When you read Extensible Markup Language (XML) data from Microsoft SQL Server by using the SqlDataReader object, the XML in the first column of the first row is truncated at 2,033 characters. You expect all of the contents of the XML data to be contained in a single row and column.
CAUSE
This behavior occurs because, for XML results greater than 2,033 characters in length, SQL Server returns the XML in multiple rows of 2,033 characters each.
RESOLUTION
To resolve this problem, use the ExecuteXmlReader method to read FOR XML queries. For additional information about how to use ExecuteXmlReader with SQL Server FOR XML queries, click the article numbers below to view the articles in the Microsoft Knowledge Base:
316016 How to use the ExecuteXmlReader method of the SqlCommand class in Visual Basic .NET
316701 How to use the ExecuteXmlReader method of the SqlCommand class in Visual C# .NET
307224 How to use XML in connected and disconnected ADO.NET applications
STATUS
This behavior is by design.
MORE INFORMATION

Steps to reproduce the problem

  1. Create a new Visual Basic Console application. Module1.vb is added by default.
  2. Add the following code to the top of the module, above the Module Module1 declaration:
    Imports SystemImports System.Data.SqlClient					
  3. Paste the following code into the Sub Main procedure of Module1:
       Dim strSQL As String = "SELECT * FROM Orders FOR XML AUTO, ELEMENTS"   Dim sbXML As New System.Text.StringBuilder()   Dim dr As SqlDataReader   Dim cn As New SqlConnection("data source=SqlServerName;user id=login;password=password;initial catalog=Northwind;")   Dim cmd As New SqlCommand(strSQL, cn)   cn.Open()   dr = cmd.ExecuteReader   'Read the first Row   dr.Read()   'Get the first Column (index 0)   sbXML.Append(dr.GetString(0))   'Uncomment the following lines to retrieve all of the results   'Do While dr.Read()   '    sbXML.Append(dr.GetString(0))   'Loop   Console.WriteLine("Number of characters retrieved: " + sbXML.Length.ToString)   Console.ReadLine()   dr.Close()   cn.Close()					
  4. Modify the following line of code in the preceding code sample to connect to the computer that is running SQL Server by using your actual login information:
       Dim cn As New SqlConnection("data source=SqlServerName;user id=login;password=password;initial catalog=Northwind;")					
  5. Press F5 to build and run the project.

    "Number of characters retrieved: 2033" is displayed in the Console window, indicating that only the first 2,033 characters of the XML contents were returned.

    Press the ENTER key to close the Console window and return to the development environment.
  6. Uncomment the following lines of code to retrieve all of the characters:
    'Do While dr.Read()'    sbXML.Append(dr.GetString(0))'Loop					
  7. Press F5 to rebuild and run the project.

    All of the XML contents are retrieved, as demonstrated by the number displayed in the Console window.
  8. Press ENTER to close the Console window and return to the development environment.
Properties

Article ID: 310378 - Last Review: 03/13/2006 07:30:26 - Revision: 2.1

Microsoft ADO.NET 2.0, Microsoft ADO.NET (included with the .NET Framework)

  • kbtshoot kbdatabase kbprb KB310378
Feedback
/c1.microsoft.com/c.gif?DI=4050&did=1&t=">