Microsoft KB Archive/310378

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

Article ID: 310378

Article Last Modified on 3/13/2006

-

APPLIES TO


 * Microsoft ADO.NET 2.0
 * Microsoft .NET Framework 1.1 Service Pack 1

-



This article was previously published under Q310378



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.



Steps to reproduce the problem
 Create a new Visual Basic Console application. Module1.vb is added by default.  Add the following code to the top of the module, above the Module Module1 declaration: Imports System Imports System.Data.SqlClient   Paste the following code into the Sub Main procedure of Module1: Dim strSQL As String = &quot;SELECT * FROM Orders FOR XML AUTO, ELEMENTS&quot; Dim sbXML As New System.Text.StringBuilder

Dim dr As SqlDataReader Dim cn As New SqlConnection(&quot;data source=SqlServerName;user id=login;password=password;initial catalog=Northwind;&quot;) 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(&quot;Number of characters retrieved: &quot; + sbXML.Length.ToString) Console.ReadLine

dr.Close cn.Close   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(&quot;data source=SqlServerName;user id=login;password=password;initial catalog=Northwind;&quot;)  Press F5 to build and run the project.

&quot;Number of characters retrieved: 2033&quot; 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.</li>  Uncomment the following lines of code to retrieve all of the characters: 'Do While dr.Read '   sbXML.Append(dr.GetString(0)) 'Loop </li> 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.</li> Press ENTER to close the Console window and return to the development environment.</li></ol>

Keywords: kbtshoot kbdatabase kbprb KB310378

-

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

© Microsoft Corporation. All rights reserved.