SqlBulkCopy.WriteToServer not reliably obeying BulkCopyTimeout
I had this problem latter and for resolving this problem set BulkCopyTimeout to zero.
bulkCopy.BulkCopyTimeout = 0;
Have you tried passing in the SqlBulkOptions.TableLock option to SqlBulkCopy? That option (quote) means it will:
Obtain a bulk update lock for the duration of the bulk copy operation.
So, if there is another processing locking the table, it would prevent the lock being gained and in theory, reliably timeout.
Update:
I set up my own test harness and can't reproduce. To lock the table, I started a transaction in SSMS doing a SELECT * FROM TargetTable WITH (HOLDLOCK)
. I used the same BulkCopy method you included in the question, using internal transactions, with a bulk load timeout of 30 seconds. Each attempt to do the bulk copy times out as expected after 30 seconds. It then succeeds when I rollback the SSMS transaction.
I was using SQL Server 2008 Express, .NET 3.5.
It's not something like after the first attempt, the bulk load timeout is not being passed in correctly? i.e. it's not somehow being set to "indefinite".
Update 2:
Also switched on Multiple Active Result Sets support in the connection string, still consistently times out for me each time.