Search code examples
c#sql-servertransactionssmo

Transactions in SMO.Server ConnectionContext not working for fatal severity

using System;
using System.Data.SqlClient;
using System.IO;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;

namespace ScriptRunner
{
    class Program
    {
        static void Main(string[] args)
        {
            var script = File.ReadAllText("Test.sql");
            const string sqlConnectionString = @"Data Source=my\ds;
                    Initial Catalog=myic;
                    Connection Timeout=0;
                    Integrated Security=true";
            SqlConnection connection = null;
            Server server = null;
            try
            {
                connection = new SqlConnection(sqlConnectionString);
                server = new Server(new ServerConnection(connection));
                server.ConnectionContext.BeginTransaction();
                server.ConnectionContext.ExecuteNonQuery(script);
                server.ConnectionContext.CommitTransaction();
            }
            catch { server.ConnectionContext.RollBackTransaction(); }
            finally { connection?.Dispose(); }
        }
    }
}

The code works perfectly, except when the error is fatal.

What I mean by fatal: when inside the Test.sql, there is:

insert into TestTable (result) values ('transaction commited')
raiserror('tset', 17, -1) with log

it works - the transaction is rolled back, nothing new in TestTable

but when inside Test.sql is fatal raiserror (20):

insert into TestTable (result) values ('transaction commited')
raiserror('tset', 20, -1) with log

the insert is beeing commited to database, like there were no transaction.

This is the spinoff of question How to use transactions in SMO.Server ConnectionContext where I had fatal error in script and thought that transactions are not working at all.

The question: How to make this code handle fatal errors? Thanks.

Solution

  • The problem was that the fatal error is supposed to terminate process, which terminates connection. As the XACT_ABORT was not set to ON, the behaviour was as described (weird nevertheless, I'd rather expect transaction hanging..). Setting SET XACT_ABORT ON at the begining of the script fixes the problem. References: