SMO: ServerConnection.CommitTransaction() Method

SMO: ServerConnection.CommitTransaction() Method by DanMaxic

Active

Type:	Bug
ID:	776674
Opened:	1/15/2013 11:41:27 PM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

SMO: ServerConnection.CommitTransaction() Method.
CommitTransaction() excecute the follwing t-sql code: if (@@trancount > 0) COMMIT TRANSACTION
So if from the code you are tring to commit transaction that is not open it will not be failed,
It sould rair error message 3902: The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

Details (expand)

English

SQL Server 2012 - Enterprise Edition

Tools (SSMS, Agent, Profiler, Migration, etc.)

Windows Server 2008 (all editions)

US English

C# Code :
---------------------------------
try run via the
SqlConnection sqlcon = new SqlConnection(TB1.Text);
            ServerConnection srvCon = new ServerConnection(sqlcon);
            Microsoft.SqlServer.Management.Smo.Server Instance = new Server(srvCon);
            srvCon.InfoMessage += new SqlInfoMessageEventHandler(OnInfoMessage);
                Log(" Opening Transaction");
                Instance.ConnectionContext.BeginTransaction();
                Instance.ConnectionContext.ExecuteNonQuery(TCode.Text);
                Log("Done     TransactionDepth " + Instance.ConnectionContext.TransactionDepth.ToString());
                Instance.ConnectionContext.CommitTransaction();
                Log("     Transaction Commited");
                Log("     TransactionDepth " + Instance.ConnectionContext.TransactionDepth.ToString());
-------------------------------
in the Instance.ConnectionContext.ExecuteNonQuery(TCode.Text); the t-sql code :
---------------------------------
BEGIN Transaction
GO
COMMIT TRANSACTION
--PRINT convert(varchar(10),@@TRanCount)
IF @@TRanCount >0
BEGIN
    print 'ROLLBACK TRAN'
        ROLLBACK TRANSACTION
END

no exception,from the log we get that the tran count is 0.
from the SqlInfoMessageEventHandler we are getting :
1/16/2013 09:05:11: InfoMessage from VM-SQL-STG-01, Source =.Net SqlClient Data Provider; Class =0; State=1; Error Number (if any)=0; Line number5; Message =ROLLBACK TRAN

in the c# code ->
Instance.ConnectionContext.CommitTransaction();
we soppose to get exception: message 3902: The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

0 attachments

Hello

We took a look at this DCR along with several others. Unfortunately triaging it against other critical DCRs I do not think we would get to investigate and implement this in the near future, so we decided not to proceed with this DCR in the next SQL Server release.

However, we have taken note of this internally, and when we revisit this functionality in the future, we will try and get this implemented.

Thanks for writing to Microsoft.
Alex Grach (MSFT)