Menu
I'm using SqlBulkCopy to restore tables from xml backups. One of the table backup is ~200MB large and has a lot of records.
I'm having error:
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. 
255
The SqlBulkCopy class lets you write managed code solutions that provide similar functionality. There are other ways to load data into a SQL Server table (INSERT statements, for example), but SqlBulkCopy offers a significant performance advantage over them.
BatchSize = 4000; By default, SqlBulkCopy will process the operation in a single batch. If you have 100000 rows to copy, 100000 rows will be copied at once. Not specifying a BatchSize can impact your application: Decrease SqlBulkCopy performance.
By default, a bulk copy operation is its own transaction. When you want to perform a dedicated bulk copy operation, create a new instance of SqlBulkCopy with a connection string, or use an existing SqlConnection object without an active transaction.
Upload the data to the temporary table, then perform the SqlBulkCopy update. Using SqlBulkCopy(), upload the datatable's data to the temporary table. Then execute a SQL command to update the main table's data from the temporary table. Finally drop the temporary table.
You probably need to increase the timeout. Try increasing the value of sqlBulkCopy.BulkCopyTimeout from the default which is 30 seconds.
112
There are two ways to fix this error:
Increase Timeout by default it is 30 second and 0 means infinite.
sqlBulkCopy.BulkCopyTimeout = {time in seconds} Decrease BatchSize by default it try to insert all rows in one batch
sqlBulkCopy.BatchSize = {no of rows you want to insert at once} http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.batchsize.aspx http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.bulkcopytimeout.aspx
21
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
