none
Error with executing SQL statements and Transactions through MSMQ Listener RRS feed

  • Question

  • I am encountering error: "The transaction associated with the current connection has completed but has not been disposed.  The transaction must be disposed before the connection can be used to execute SQL statements."

    The scenario is this (note: all SQL statements are done via stored procedures and repository pattern):

    • I have an MSMQ Listener that listens for messages in queue
    • When a message is received (e.g. "DATATABLE1"), it starts an SQL Job that downloads data from a LARGE CSV file based on the message (e.g. "DATATABLE1.csv")
    • It then updates a record in the database pertaining to this particular file, just to flag that it is already downloading (for use by other components of the system).
    • The program then sleeps and loops to check if the program is already done downloading (by querying the Job execution status). If it is not yet done, it sleeps again and just repeats the process until the Job has executed successfully.

    The problem is, the program can query SQL while the Job is running. The moment the Job has executed successfully, the next time the program queries SQL to check the status of the job, it throws the error I mentioned above.

    Here's a rough sample of my code:

    Program Start:

    listener.Start(
        m =>
        {
            using (var downloadService = new DownloadService())
            {
                if (downloadService.Import(m.Message))
                    return true;
                else
                    return false;
            }
        }
    );

    Listener:

    private void OnPeekCompleted(object sender, PeekCompletedEventArgs e)
    {
        try
        {
            _messageQueue.EndPeek(e.AsyncResult);
        }
        catch (Exception ex)
        {
            //do error handling
        }
    
        var messageId = string.Empty;
    
        try
        {
            using (TransactionScope transaction = new TransactionScope())
            {
                Message message;
                if(_strictTransaction)
                    message = _messageQueue.Receive(MessageQueueTransactionType.Automatic);
                else
                    message = _messageQueue.Receive(MessageQueueTransactionType.Single);
    
                if (message != null)
                {
                    messageId = message.Id;
                    var reader = new StreamReader(message.BodyStream);
                    var jsonMessage = reader.ReadToEnd();
                    var concreteMessage = JsonConvert.DeserializeObject<T>(jsonMessage, new JsonSerializerSettings
                    {
                        TypeNameHandling = TypeNameHandling.All
                    });
    
                    if (ProcessMessage(concreteMessage))
                        transaction.Complete();
                    else
                        throw new ApplicationException("Message did not complete.");
                }
            }
            
        }
        catch (Exception ex)
        {
           //do error handling
        }
    
    }
    
    private bool ProcessMessage(T body)
    {
        if (_messageHandler != null)
        {
            return _messageHandler(body);
        }
    
        return false;
    }

    DownloadService:

    public bool Download(string fileName)
    {
        _dbRepo.StartJob(fileName, JobType.Download); //Starts the job that downloads content
    
        _dataRepo.UpdateStatus(fileName, true); //flags record for the file to Downloading
    
        WaitJobToComplete:
        Thread.Sleep(3000);
    
        if (_dataRepo.Downloading(fileName)) //this is where ERROR occurs! it only occurs once the Job has successfully completed, but continues as normal while the Job is running
        {
            var jobStatus = _dbRepo.GetJobExecution(fileName, JobType.Download); //queries Job status
            
            if (jobStatus.CurrentExecutionStatus == 4)
            {
                _dataRepo.UpdateStatus(fileName, false); //flags record for the file to Complete
    
                return true;
            }
    
            goto WaitJobToComplete;
        }
        
        return false;
    }

    I have searched and already tried the most common below solutions I found to no avail:

    • increased time out in machine.config
    • increased time out in TransactionScope
    • increased sleep time to wait until Job completes (I thought the error had something to do with it continuously querying every 3 sec)

    Any help would be appreciated as well as any possible solutions.


    Monday, June 17, 2019 3:44 AM