Msg 40197, Level 20, State 1, Line 16
The service has encountered an error processing your request. Please try again. Error code 40549.
The file was big and error doesn’t come immediately. It takes several hours before we get error. I looked into the message I found 40549 which is another message number.
select * from sys.messages where message_id = 40549
Here is what it means.
Session is terminated because you have a long running transaction. Try shortening your transaction.
And now it makes perfect sense. Here was the T-SQL which they were using.
INSERT INTO HumanResource.EmployeeMaster SELECT * FROM OPENROWSET(BULK 'Employee.dat', DATA_SOURCE = 'blobsource', FORMATFILE='fmt/Employee.xml', FORMATFILE_DATA_SOURCE = 'blobsource') as data;
As per the error message and behavior, we need to reduce the size of batch to make the transaction shorter. When I searched ways to do that in BCP I landed up https://technet.microsoft.com/en-us/library/ms188267(v=sql.105).aspx (Managing Batches for Bulk Import). I found ROWS_PER_BATCH parameter which worked like a charm.
Reference: Pinal Dave (http://blog.SQLAuthority.com)