CODEWITHSUNDEEP
c#sql-server
Steve Wash
Steve Wash

Reputation: 986

Unable to Rollback Transaction: This SqlTransaction has completed

I have the following code for copying data from one server to a different server:

private static string CopyData(string sourceConnection, string targetConnection, bool push = true)
{
    string result = "Copy started";
    SqlConnection source = new SqlConnection(sourceConnection);
    SqlConnection target = new SqlConnection(targetConnection);
    SqlTransaction targetTransaction;

    source.Open();
    target.Open();

    if (source.State != ConnectionState.Open || target.State != ConnectionState.Open)
    {
        throw new Exception("Unable to connect to server at this time.");
    }

    targetTransaction = target.BeginTransaction();

    try
    {
        ClearTable(target, targetTransaction, "TableAAA");
        ClearTable(target, targetTransaction, "TableBBB");

        CopyTable(source, target, targetTransaction, "TableAAA");
        CopyTable(source, target, targetTransaction, "TableBBB");

        targetTransaction.Commit();
        result = "Copy successful";
    }
    catch (Exception E)
    {
        targetTransaction.Rollback();
        result = "An SQL Error has occurred. Unable to copy data at this time.\n\n" + E.Message;            
    }
    finally
    {
        target.Close();
        source.Close();
    }

    return result;
}

private static void ClearTable(SqlConnection destination, SqlTransaction tran, string table)
{
    SqlCommand cmd = new SqlCommand(string.Format("DELETE FROM {0}", table), destination);
    cmd.Transaction = tran;
    cmd.ExecuteNonQuery();
}

private static void CopyTable(SqlConnection source, SqlConnection destination, SqlTransaction tran, string table)
{
    SqlCommand cmd = new SqlCommand(string.Format("DELETE FROM {0}", table), destination);            
    cmd.Transaction = tran;
    cmd.ExecuteNonQuery();

    cmd = new SqlCommand(string.Format("SELECT * FROM {0}", table), source);
    SqlDataReader reader = cmd.ExecuteReader();

    SqlBulkCopy bulkData = new SqlBulkCopy(destination, SqlBulkCopyOptions.Default, tran);
    bulkData.DestinationTableName = table;
    bulkData.BulkCopyTimeout = (int)Properties.Settings.Default.CommandTimeOut;
    bulkData.WriteToServer(reader);

    bulkData.Close();
    reader.Close();
}

If I force an error by changing the schema of one of the tables, I get the error "This SqlTransaction has completed" when it attempts to rollback any changes. How do I correct this problem and why is it happening?

Upvotes: 1

Views: 485

Answers (1)

Matthew
Matthew

Reputation: 25743

I'm not sure of the exact problem you're having, but I would recommend you rewrite your code in such a way that it uses the using statement. This would prevent you from needed to explicitly close your connections or rollback your transactions.

private static string CopyData(string sourceConnection, string targetConnection, bool push = true)
{       
    using (var source = new SqlConnection(sourceConnection))
    using (var target = new SqlConnection(targetConnection))
    {
        source.Open();
        target.Open();

        // no need to check for open status, as Open will throw an exception if it fails

        using (var transaction = target.BeginTransaction())
        {
            // do work 

            // no need to rollback if exception occurs
            transaction.Commit();
        }

        // no need to close connections explicitly
    }
}

Upvotes: 2

Related Questions