Hi, I'm using sqlbulkcopy to load data into a sql2008 database, and we're getting timeout errors loading larger tables. Some tables which were loading OK are failing since indexes have been added to them. The source data is a Informix datareader. I've tried setting large values in both the connection timeout and the sqlbulkcopy.bulkcopytimeout, but the timeout usually occurs within 60 seconds, well below the timeout values I've set (240 secs). I've tried setting the .bulcopytimeout property to zero but it makes no difference. Very occasionally a load will take place - the last large table to load took 7 minutes.

So I suspect the problem is somewhere else - any clues gratefully received - here's the code. I've reduced the batch size to 500.

The documentation on bulkcopytimeout that I have found does not describe what the property actually means - is it the maximum time to complete the entire copy or to complete one batch or what ?

Here's some extracts from my error log - the first shows a successful large table load, follow by timeout failures.

24/11/2009 10:59:01
Level : Information
MISLoad - Starting load from Mach4 to ParagonMIS of all selected tables
---------------------------------------
24/11/2009 11:07:38
Level : Warning
MISLoad - table load warning.
Row count mismatched for stockmove - 4730598 rows read, 4730627 rows written
---------------------------------------
24/11/2009 11:07:38
Level : SuccessAudit
MISLoad - Successful load from Mach4 to ParagonMIS of all selected tables

24/11/2009 11:09:46
Level : Information
MISLoad - Starting load from Mach4 to ParagonMIS of all selected tables
---------------------------------------
24/11/2009 11:10:17
Level : Error
MISLoad - table load failed.
Microsoft SQL Server Exception in MISLoadClasses.BulkCopy of table poline -
System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at MISLoadClasses.BulkCopy.LoadData(String TableName, Int32 intBulkCopyTimeout, Int32 intBulkCopyBatchSize)
---------------------------------------
24/11/2009 11:10:52
Level : Error
MISLoad - table load failed.
Microsoft SQL Server Exception in MISLoadClasses.BulkCopy of table wodet -
System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParserStateObject.ReadNetworkPacket()
at System.Data.SqlClient.TdsParserStateObject.ReadBuffer()
at System.Data.SqlClient.TdsParserStateObject.ReadByte()
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at MISLoadClasses.BulkCopy.LoadData(String TableName, Int32 intBulkCopyTimeout, Int32 intBulkCopyBatchSize)
---------------------------------------

Here's the core of the code - I'm setting the bulktimeout and batch sizes using parameters that are set by the user and passed in to this routine - this routine is within a loop thatpasses the names of the tables to load.

Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(GlobalData.misDbConnectString, SqlBulkCopyOptions.TableLock)

bulkCopy.BatchSize = intBulkCopyBatchSize

bulkCopy.DestinationTableName =

"dbo." & TableName

bulkCopy.BulkCopyTimeout = intBulkCopyTimeout

' Write from the source to the destination.

bulkCopy.WriteToServer(reader2)

End Using

---

Don't judge me, just Upgrade me. Thanks!

I've unmarked this as an answer. The download you suggested does not work. We get a message "The upgrade patch cannot be installed by the Windows Installer Service because the program to be upgraded may be missing or the upgrade patch may be update a different version of the program..."

We are running SQL Server 2008 on Windows Server 2003, .NET Framework 2.0.

Also this is a Windows Forms app not an ADO app, if that makes any difference.

Any other clues please ?