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

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 System
   Imports 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.

Back to the top