I was working on XML feeds for my site and found a strange problem.
The XML returned by the SP was getting truncated. After ‘googling’ for a while, I found this on MSDN:
The XML data row is truncated at 2,033 characters when you use the SqlDataReader object
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.
This was exactly my problem. I found the solution as well. The solution is to use ExecuteXmlReader instead of SqlDataReader .
Dim con As New System.Data.SqlClient.SqlConnection
con.ConnectionString = "whatever"
Dim cmd As New System.Data.SqlClient.SqlCommand
cmd.Connection = cn
cmd.CommandText = "SPName or SQL Statement"
Dim ObjXMLReaderAs System.Xml.XmlReader
Dim OutPut As String
Do While ObjXMLReader.ReadState <> System.Xml.ReadState.EndOfFile
OutPut &= ObjXMLReader .ReadOuterXml()