SqlBulkCopy.WriteToServer() keep getting "connection is closed"

c# sqlbulkcopy sql-server

Question

This makes no sense to me but maybe someone with keener eyes can spot the problem.

I have a Windows service that uses FileSystemWatcher. It processes some files and uploads data to an MSSQL database. It works totally fine on my machine -- detached from Visual Studio (ie not debugging) and running as a service. If copy this compiled code to our server, and have it point to the same database, and even the same files (!), I get this error every single time:

System.InvalidOperationException: Invalid operation. The connection is closed.
   at System.Data.SqlClient.SqlConnection.GetOpenTdsConnection()
   at System.Data.SqlClient.SqlBulkCopy.CopyBatchesAsyncContinuedOnError(Boolean cleanupParser)
   at System.Data.SqlClient.SqlBulkCopy.<>c__DisplayClass30.<CopyBatchesAsyncContinuedOnSuccess>b__2c()
   at System.Data.SqlClient.AsyncHelper.<>c__DisplayClass9.<ContinueTask>b__8(Task tsk)

I have tried pointing my local code to the server's files and it works fine. .Net 4.5.1 is on both machines. Services are both running under the same domain user. It is baffling. Perhaps there is something I don't understand about SqlBulkCopy.WriteToServerAsync()? Does it automatically share connections or something? Does it close in between calls or something? Here's the relevant code:

private static void ProcessFile(FileInfo fileInfo)
{
    using (var bulkCopy = new SqlBulkCopy("Data Source=myserver;Initial Catalog=mydb;Persist Security Info=True;User ID=myusr;Password=mypwd;")
    using (var objRdr = ObjectReader.Create(ReadLogFile(fileInfo)
                                    .Where(x => !string.IsNullOrEmpty(x.Level)),
                                         "Id", "AppId", "AppDomain", "AppMachine",
                                         "LocalDate", "UtcDate", "Thread", "Level", "Logger", "Usrname",
                                         "ClassName", "MethodName", "LineNo", "Message", "Exception",
                                         "StackTrace", "Properties"))
    {
        bulkCopy.DestinationTableName = "EventLog";
        bulkCopy.BulkCopyTimeout = 600;
        bulkCopy.EnableStreaming = true;
        bulkCopy.BatchSize = AppConfig.WriteBatchSize;
        bulkCopy.WriteToServerAsync(objRdr).ContinueWith(t =>
            {
                if (t.Status == TaskStatus.Faulted)
                {
                    CopyToFailedDirectory(fileInfo);
                    _log.Error(
                        string.Format(
                            "Error copying logs to database for file {0}. File has been copied to failed directory for inspection.",
                            fileInfo.FullName), t.Exception.InnerException ?? t.Exception);
                    Debug.WriteLine("new handle error {0}",
                                    (t.Exception.InnerException ?? t.Exception).Message);
                }
                if (t.Status == TaskStatus.RanToCompletion)
                {
                    _log.InfoFormat("File {0} logs have been copied to database.", fileInfo.FullName);
                    Debug.WriteLine("Yay, finished {0}!", fileInfo.Name);
                }
                // if this is the last one, delete the original file
                if (t.Status == TaskStatus.Faulted || t.Status == TaskStatus.RanToCompletion)
                {
                    Debug.WriteLine("deleting file {0}", fileInfo.Name);
                    PurgeFile(fileInfo);
                }
            });
    }
}

Couple notes in case you ask:

  • ObjectReader is a FastMember IDataReader implementation. CRAZY fast. It reads the file into custom objects with the properties you see listed.
  • It throws the error for every single file.
  • Again, this works on my machine, both as a service and as a console app. I even had it working once on the server. It threw the error and never worked again.

Any ideas?

Accepted Answer

Looks like an issue with it being Async.

Please let me know if I wrong, but what I noticed is you have your SqlBulkCopy and ObjectReader in a using statement which is great, however, you are doing all the processing asynchronously. Once, you call it and it starts doing work, your using statements are disposing of your objects which will also kill your connection.

The odd thing is that it sounds like it works sometimes, but perhaps it just becomes a race condition at that point.


Popular Answer

This looks to me to be a bug in the SqlBulkCopy implementation. If you run a (large) number of bulk copies in parallel in separate tasks concurrently, disable your network connection and then trigger a full garbage collection, you will reliably get this exception thrown on the GC's finalizer thread. It is completely unavoidable.

That shouldn't happen because you are continuing the WriteToServerAsync task and handling the fault. But in the implementation, on error they start a new task that they don't continue or await.

This still seems to be a bug in .NET 4.6.2.

The only fix I can see is to subscribe to TaskScheduler.UnobservedTaskException and look for something in the stacktrace that identifies the issue. That isn't a fix by the way, it is a hack.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why