title: Out of Memory when reading a string from SqlDataReader
body: <p>I'm running into the strangest thing that I can't figure out. I have a SQL table with a bunch of reports stored in an ntext field. When I copied and pasted the value of one of them into notepad and saved it (used Visual Studio to grab the value from a smaller report in a differente row), the raw txt file was about 5Mb. When I try to get this same data using SqlDataReader and convert it to a string, I get an out of memory exception. Here is how I am trying to do it:</p>
  
  <pre><code>string output = "";
  string cmdtext = "SELECT ReportData FROM Reporting_Compiled WHERE CompiledReportTimeID = @CompiledReportTimeID";
  SqlCommand cmd = new SqlCommand(cmdtext, conn);
  cmd.Parameters.Add(new SqlParameter("CompiledReportTimeID", CompiledReportTimeID));
  SqlDataReader reader = cmd.ExecuteReader();
  while (reader.Read())
  {
      output = reader.GetString(0); // &lt;--- exception happens here
  }
  reader.Close();
  </code></pre>
  
  <p>I tried creating an object and a stringbuilder to grab the data, but I still get the same out of memory exception. I've also tried using reader.GetValue(0).ToString() as well to no avail. The query only returns 1 row, and when I run it in SQL Management Studio its as happy as can be.</p>
  
  <p>The exception thrown is:</p>
  
  <pre><code>System.OutOfMemoryException was unhandled by user code  
  Message=Exception of type 'System.OutOfMemoryException' was thrown.  
  Source=mscorlib  
   StackTrace:  
   at System.String.CreateStringFromEncoding(Byte* bytes, Int32 byteLength, Encoding       encoding)  
     at System.Text.UnicodeEncoding.GetString(Byte[] bytes, Int32 index, Int32 count)  
     at System.Data.SqlClient.TdsParserStateObject.ReadString(Int32 length)  
     at System.Data.SqlClient.TdsParser.ReadSqlStringValue(SqlBuffer value, Byte type, Int32 length, Encoding encoding, Boolean isPlp, TdsParserStateObject stateObj)  
     at System.Data.SqlClient.TdsParser.ReadSqlValue(SqlBuffer value, SqlMetaDataPriv md, Int32 length, TdsParserStateObject stateObj)  
     at System.Data.SqlClient.SqlDataReader.ReadColumnData()  
     at System.Data.SqlClient.SqlDataReader.ReadColumn(Int32 i, Boolean setTimeout)  
     at System.Data.SqlClient.SqlDataReader.GetString(Int32 i)  
     at Reporting.Web.Services.InventoryService.GetPrecompiledReportingData(DateTime ReportTime, String ReportType) in   C:\Projects\Reporting\Reporting.Web\Services\InventoryService.svc.cs:line 3244  
     at SyncInvokeGetPrecompiledReportingData(Object , Object[] , Object[] )  
     at System.ServiceModel.Dispatcher.SyncMethodInvoker.Invoke(Object instance, Object[] inputs, Object[]&amp; outputs)  
     at System.ServiceModel.Dispatcher.DispatchOperationRuntime.InvokeBegin(MessageRpc&amp; rpc)  
   InnerException:   
      null
  </code></pre>
  
  <p>I had tested with other row numbers that appeared to work, but that was a false positive as those test ID's had no data. I pulled some other test ID's after looking at the table that contain reports that are near identical, and I get the same exception. Maybe its how the string is encoded? The data stored in the table is a JSON encoded string that was generated out of a really gnarly class I made somewhere else, in case that helps.</p>
  
  <p>Here is the preceding code block:</p>
  
  <pre><code>// get the report time ID
  int CompiledReportTimeTypeID = CompiledReportTypeIDs[ReportType];
  int CompiledReportTimeID = -1;
  cmdtext = "SELECT CompiledReportTimeID FROM Reporting_CompiledReportTime WHERE CompiledReportTimeTypeID = @CompiledReportTimeTypeID AND CompiledReportTime = @ReportTime";
  cmd = new SqlCommand(cmdtext, conn);
  cmd.Parameters.Add(new SqlParameter("CompiledReportTimeTypeID", CompiledReportTimeTypeID));
  cmd.Parameters.Add(new SqlParameter("ReportTime", ReportTime));
  reader = cmd.ExecuteReader();
  while (reader.Read())
  {
      CompiledReportTimeID = Convert.ToInt32(reader.GetValue(0));
  }
  reader.Close();
  </code></pre>
  
  <p>CompiledReportTypeIDs is a dictionary that gets the correct CompiledReportTimeTypeID based on a string parameter that's fed in at the beginning of the method. ReportTime is a DateTime that is fed in earlier.</p>
  
  <p>Edit:
  I am going to drop the table and recreate it with the ReportData field as nvarchar(MAX) instead of ntext, just to rule out a SQL data type issue. It's a long shot and I'll update again with what I find.</p>
  
  <p>Edit2:
  Changing the field in the table to nvarchar(max) had no effect. I also tried using output = cmd.ExecuteScalar().ToString() as well, with no impact. I'm trying to see if there is a max size for SqlDataReader. When I copied the value of the text from SQL Mgmt Studio, it was only 43Kb when saved in notepad. To verify this, I pulled a report with a known working ID (a smaller report), and when I copied the value straight out of Visual Studio and dumped it in notepad it was around 5MB! That means these big reports are probably in the ~20MB range sitting in a nvarchar(max) field.</p>
  
  <p>Edit3:
  I rebooted everything, to include my dev IIS server, the SQL server, and my dev laptop. Now it seems to be working. This isn't the answer as to why this happened though. I'm leaving this question open for explanations as to what happened, and I'll mark one of those as an answer.</p>
  
  <p>Edit4:
  Having said that, I ran another test without changing a thing and the same exception has returned. I'm really starting to think that this is a SQL issue. I'm updating the tags on this question. I made a separate app that runs the exact same query and it runs fine. </p>
  
  <p>Edit5:
  I have implemented sequential access as per one of the answers below. Everything gets read into a stream properly, but when I try to write it out to a string I'm still getting the out of memory exception. Would this indicate the issue of getting a contiguous block of memory? Here is how I implemented the buffering:</p>
  
  <pre><code>                reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess);
              long startIndex = 0;
              long retval = 0;
              int bufferSize = 100;
              byte[] buffer = new byte[bufferSize];
              MemoryStream stream = new MemoryStream();
              BinaryWriter writer = new BinaryWriter(stream);
              while (reader.Read())
              {
                  // Reset the starting byte for the new CLOB.
                  startIndex = 0;
  
                  // Read bytes into buffer[] and retain the number of bytes returned.
                  retval = reader.GetBytes(0, startIndex, buffer, 0, bufferSize);
  
                  // Continue while there are bytes beyond the size of the buffer.
                  while (retval == bufferSize)
                  {
                      writer.Write(buffer);
                      writer.Flush();
  
                      // Reposition start index to end of last buffer and fill buffer.
                      startIndex += bufferSize;
                      retval = reader.GetBytes(0, startIndex, buffer, 0, bufferSize);
                  }
  
                  //output = reader.GetString(0);
              }
              reader.Close();
              stream.Position = 0L;
              StreamReader sr = new StreamReader(stream);
              output = sr.ReadToEnd(); &lt;---- Exception happens here
              //output = new string(buffer);
  </code></pre>
  
  <p>Edit6:
  To add to this, when OOM exception happens I see the IIS worker process (which holds the method that is running) hit almost 700MB. This is running on IIS Express and not the full IIS on the production server. Would this have anything to do with it? Also when I call Byte[] data = stream.ToArray() I intermittently get the OOM as well. I think what I really need is a way to give more memory to this process, but I don't know where to configure this.</p>
  
  <p>Edit7:
  I just changed my dev server from using IIS Express on my local machine to the built-in Visual Studio web server. The OOM exception is now gone. I really think it was the allocating a contiguous block of memory issue, and for whatever reason IIS Express wouldn't fork it over. Now that it is running fine, I will publish to my full blown server on 2008R2 running the regular IIS7 to see how it goes.</p>