I am trying to upload 45k records through csv(or excel) using SQLBulkCopy class. Once the data goes into a table I am updating a field in all 45k records based on some logic(we are using stored procedure for that). But when I do that, I am getting this error - Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
I have done some R & D on the same and based on that I have changed some tags in web.config file like
1. Change the value of maxItemsInObjectGraph attribute in dataContractSerializer tag to 2,147,483,647.
2. Change the value of closeTimeout, openTimeout, receiveTimeout, sendTimeout, maxBufferSize, maxBufferPoolSize and maxReceivedMessageSize attribute in binding tag tag to 2,147,483,647.
3. Change the value of maxDepth, maxStringContentLength, maxArrayLength, maxBytesPerRead and maxNameTableCharCount in readerQuotas tag. You can check both the binding tags in web.config file tag to 2,147,483,647.
4. Add an attribute executionTimeout="600" in HTTPRuntime .
5. Set Connection Timeout=90; in connection string
Note - There is one more point that I want to highlight is when I run the stored procedure through SSMS I can run it successfully and there is no time out error.
Still I am getting the same issue. Is there anything that I have missed? Please advice.
Hello, my name is Monikandan. I'm a 40 year old self-employed ML Group from the Nagercoil.
ou can explicitly set BulkCopyTimeout
ReplyDeleteusing (SqlBulkCopy bulkCopy = new SqlBulkCopy(connStr, SqlBulkCopyOptions.UseInternalTransaction))
{
bulkCopy.BulkCopyTimeout = 600;//e.g. 120 sec.
//... fill with data, map columns...
bulkCopy.WriteToServer(table);
// ^^^^ waits indefinitely, doesn't throw until *after*
// the lock is released.
}
Thanks.