title: SqlBulkCopy.WriteToServer not reliably obeying BulkCopyTimeout
body: <p>I need to count sequential timeout exceptions from SqlBulkCopy. To test this, I use an external app to start a transaction &amp; lock up the target table. </p>
  
  <p>Only on the first call does SqlBulkCopy throw a timeout exception when expected. We've tried using an external connection &amp; transaction, as well as using a connection string and internal transaction. With the external connection &amp; transaction, the infinite wait was never in opening the connection or beginning or committing the transaction, but always at <code>.WriteToServer()</code>.</p>
  
  <p>Is there some approach to this whereby <code>SqlBulkCopy.WriteToServer()</code> will reliably throw a timeout exception when it has reached its <code>.BulkCopyTimeout</code> limit? </p>
  
  <pre><code>public void BulkCopy(string connectionString, DataTable table, int bulkTimeout)
  {
      using (SqlBulkCopy bulkCopy = new SqlBulkCopy(
          connectionString, 
          SqlBulkCopyOptions.UseInternalTransaction))
      {
      bulkCopy.BulkCopyTimeout = bulkTimeout;//e.g. 120 sec.
      //... fill with data, map columns...
      bulkCopy.WriteToServer(table);
      // ^^^^ waits indefinitely, doesn't throw until *after*
      //      the lock is released.
      }
  }
  </code></pre>
  
  <p>I prefer to let exceptions bubble up rather than handle them in the scope of the <code>using</code> block, but I can always rethrow. Thanks much for any insight.</p>
  
  <p><strong>Update 1:</strong></p>
  
  <p>Still no resolution. Interesting behavior discovered though -- a normal SqlCommand will throw a TimeoutException as expected during the same lock that makes the SqlBulkCopy.WriteToServer method hang indefinitely.</p>
  
  <p>Here are approaches that we've tried -- and that have failed -- to get SqlBulkCopy.WriteToServer to consistently throw timeouts when expected:  </p>
  
  <ul>
  <li>MARS (Multiple Active Result Sets) on/off  </li>
  <li>TableLock on vs. off  </li>
  <li>Destination as heap table vs. indexed table  </li>
  <li>Longer/shorter BulkTimeout values (10 seconds to 5 minutes)</li>
  <li>Internal vs external transaction  </li>
  </ul>
  
  <p>For now, as a workaround, I'm alternating between a) putting the WriteToServer call in an asynchronous wrapper so I can time it myself, and b) only calling WriteToServer once; after timeouts, wait until a regular SqlCommand <em>succeeds</em> before trying WriteToServer again. Using these approaches, I'm at least able to stay in control of the execution flow.</p>