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?

1
2
6/17/2014 8:46:16 PM

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.

6
6/17/2014 8:41:45 PM

Popular Answer

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.



Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow