Timeout expired with SqlBulkCopy

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.

Contents hide

Answers:

Method 1

Method 2

Method 3

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

You probably need to increase the timeout. Try increasing the value of sqlBulkCopy.BulkCopyTimeout from the default which is 30 seconds.

Method 2

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

Method 3

Change the CommandTimeout property of your SqlCommand object.

The Connect Timeout attribute of a connection string determines how long a SqlConnection Object runs before it stops attempting to connect to a server.

Also change the SqlBulkCopy.BulkCopyTimeout Property.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating