CODEWITHSUNDEEP
c#sql-serversqlbulkcopy
ironfist
ironfist

Reputation: 947

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

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:

Any ideas?

Upvotes: 2

Views: 5304

Answers (3)

Tom Warner
Tom Warner

Reputation: 3588

Way late to throw this one on here but I was having what I thought was the same issue with SqlBulkCopy. Tried some of the steps in the other answers but with no luck. Turns out in my case that the actual error was caused by a string in the data going above the max length on one of the varchar columns, but for some reason the only error I was getting was the one about the closed connection.

Strangely, my coworker tried the same thing, and got an actual error message about the varchar being out of bounds. So we fixed the data and everything worked, but if you're here because of this error and nothing else works, you might want to start looking for different issues in your data.

Upvotes: 2

satnhak
satnhak

Reputation: 9861

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.

Upvotes: 0

TyCobb
TyCobb

Reputation: 9089

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.

Upvotes: 10

Related Questions