Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
tasmisr
Starting Member
4 Posts |
 Posted - 2006-01-26 : 09:03:57
|
| I have a stored procedure that returns xml data (using For XML), it returns about 9000 records. I use a datareader to write the data to a XML file.For some reason, the datareader doesn't write all data, it seems like the data returned has some size limit.here is my code, my code is based on a msdn example for using getBytes were it writes data to the stream by chunks based on the buffer size: '----some code here to fill in the stored procedure name and parameters. Dim mydatareader As SqlDataReader connectionObj.Open() mydatareader = CommandObj.ExecuteReader Dim fs As FileStream Dim bw As BinaryWriter Dim bufferSize As Integer = 100 Dim outChar(bufferSize - 1) As Char Dim retval As Long Dim startindex As Long = 0 If mydatareader.Read Then fs = New FileStream("c:\test.xml", FileMode.OpenOrCreate, FileAccess.Write) bw = New BinaryWriter(fs) retval = mydatareader.GetChars(0, startindex, outChar, 0, 100) ' Continue reading and writing while there are bytes beyond the size of the buffer. Do While retval = bufferSize 'bw.Write(outByte) bw.Write(outChar) bw.Flush() ' Reposition the start index to the end of the last buffer and fill the buffer. startindex += bufferSize retval = mydatareader.GetChars(0, startindex, outChar, 0, bufferSize) Loop 'Write the remaining buffer. bw.Write(outChar, 0, CType(retval - 1, Integer)) bw.Flush() ' Close the output file. bw.Close() fs.Close() End If connectionObj.Close() connectionObj.Dispose() CommandObj.Dispose() |
|
|
tasmisr
Starting Member
4 Posts |
Posted - 2006-01-26 : 09:32:09
|
| okay, here is the freking solution that wasted 8 hours of my time:http://www.aspnetresources.com/blog/executescalar_truncates_xml.aspx a geek at microsoft figured he would makes it easier by returning multiple records when you try to return xml data using sqldatareader....how bad is that... without any informative errors or warnings... |
 |
|
|
druer
Constraint Violating Yak Guru
314 Posts |
Posted - 2006-01-26 : 09:34:00
|
| Not sure if this could be the issue or not but checkout SET TEXTSIZE in books online.Are you always being truncated at the same place/number of characters? Is it always the last remaining block by chance? |
|
|
|
tasmisr
Starting Member
4 Posts |
Posted - 2006-01-26 : 09:45:38
|
| no usually I get all data.My issue was a very special case when you use:sqldatareader + execute scalar or exeutereader with single records...what ado.net does is that it returns MULTIPLE records and each record has a maximum of 2033 characters.-Best |
|
|
|
tasmisr
Starting Member
4 Posts |
Posted - 2006-01-26 : 09:47:14
|
| I forgot to add that it does this only when you try to return XML data. |
|
|
|
|
|
|
|
|
Datareader and size of the data returned