Through the use of OleDbConnection, I have a SqlBulkCopy operation that copies data from an MS-Access 2007 database to a SQL Server database. For one MS-Access database, this has worked in the past and still does, but not for the other.
I experience this issue:
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
It is difficult to accept that the oledbCommand is in timeout. The sqlBulkCopy's command timeout value is 0. BulkCopyTimeout = 0 and to the left and right (MS-Access and SQL Server the timeouts have now been set to 0).
Could the error message above be disguising any additional problems or exceptions? Is it possible to identify the root cause of a sqlBulkCopy.WriteToServer error (because there don't seem to be any inner exceptions, etc.)?
The problem was that some of the dates that were being transferred were valid for Access but invalid for SQL. For whatever reason, this was reporting as a Timeout rather than "invalid date/time"; however, if you just send a little amount of data (200 rows) as opposed to the whole transfer (500,000), it reports as invalid date/time.