3

How does one share transactions with an SMO server connection? Sharing connections is as simple as

var connection = new SqlConnection();
var serverConnection = new ServerConnection(connection);
var server = new Server(serverConnection);

But if the connection already has an existing Transaction of its own, 

var connection = new SqlConnection();
connection.BeginTransaction();

when I start using the server object

var database = server.Databases[connection.Database];

This gets thrown:

ExecuteNonQuery requires the command to have a transaction when the connection assigned to the command is in a pending local transaction

Jonn
  • 4,599
  • 9
  • 48
  • 68

1 Answers1

3

I faced the same problem and couldn't make it work either.

My workaround was creating the transaction using SQL instead. This allowed me to use the same connection and transaction in SMO and ADO.NET.

Dim srvcn As New ServerConnection(sqlcn)
Dim srv As New Server(srvcn)

Dim sql As String = "SET CONCAT_NULL_YIELDS_NULL, ANSI_NULLS, ANSI_PADDING, QUOTED_IDENTIFIER, ANSI_WARNINGS, ARITHABORT, XACT_ABORT ON " & vbCrLf & _
                    "SET NUMERIC_ROUNDABORT, IMPLICIT_TRANSACTIONS, NOEXEC OFF " & vbCrLf & _
                    "GO " & vbCrLf & _
                    "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE " & vbCrLf & _
                    "GO " & vbCrLf & _
                    "BEGIN TRANSACTION" & vbCrLf & _
                    "GO "

srv.ConnectionContext.ExecuteNonQuery(sql)

Finishing the transaction was also done via plain SQL:

srv.ConnectionContext.ExecuteNonQuery("IF @@TRANCOUNT>0 COMMIT TRANSACTION " & vbCrLf & _
                                      "GO " & vbCrLf & _
                                      "SET NOEXEC OFF " & vbCrLf & _
                                      "GO ")

I hope this might help you, even though the question is quite old...

Quant
  • 67
  • 4