I'm implementing a transactional receive from SSB queue. I used
System.Transactions.TransactionScope to simplify the transaction
management code. When I used the "WAITFOR  (RECEIVE ...), TIMEOUT
100"  statment I get TransactionAbortedException when I try to open
the second connection under the same TransactionScope block.

Note that when I remove the WAITFOR and just doing RECEIVE everything
works fine and the transaction state is guaranteed.

Does anybody know if this a known issue with WAITFOR? Does it supposed
to abort the transaction when the command completes?

See code below and stack traces I got.

Thanks,

Noam Helfman

Repro code:
...

using (TransactionScope txnScope = new
TransactionScope(TransactionScopeOption.RequiresNew))
        {
           using (SqlConnection conn1 = new
SqlConnection(TestConnectionString))
           {
              conn1.Open();

              using (SqlCommand command = conn1.CreateCommand())
              {
                 command.CommandText =
                    "WAITFOR (RECEIVE TOP(1) message_body FROM
TestReceiveQueue), TIMEOUT 100"; // <-- this causes
TransactionAbortedException below
                 //command.CommandText = "RECEIVE TOP(1) message_body
FROM TestReceiveQueue"; // <-- this works

                 command.CommandType = CommandType.Text;

                 using (SqlDataReader reader =
command.ExecuteReader())
                 {
                    while (reader.Read())
                    {
                       byte[] message = (byte[])
reader["message_body"];
                    }
                 }
              }

              using (SqlConnection conn2 = new
SqlConnection(TestConnectionString))
              {
                 conn2.Open(); // <-- TransactionAbortedException
here

                 using (SqlCommand command = conn2.CreateCommand())
                 {
                    command.CommandText = string.Format("INSERT INTO
tbl1 VALUES (1)");
                    command.CommandType = CommandType.Text;

                    command.ExecuteNonQuery();
                 }

              }

              txnScope.Complete();
           }
        }
...

Schema:

CREATE QUEUE TestReceiveQueue WITH STATUS=ON ,RETENTION=OFF;
CREATE TABLE tbl1 (col1 int not null);

Stack traces:
System.Data.SqlClient.SqlException: Cannot promote the transaction to
a distributed transaction because there is an active save point in
this transaction.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception,
Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
SqlCommand cmdHandler, SqlDataReader dataStream,
BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject
stateObj)
at
System.Data.SqlClient.TdsParser.TdsExecuteTransactionManagerRequest(Byte[]
buffer, TransactionManagerRequestType request, String transactionName,
TransactionManagerIsolationLevel isoLevel, Int32 timeout,
SqlInternalTransaction transaction, TdsParserStateObject stateObj)
at
System.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransactionYukon(TransactionRequest
transactionRequest, String transactionName, IsolationLevel iso,
SqlInternalTransaction internalTransaction)
at
System.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransaction(TransactionRequest
transactionRequest, String name, IsolationLevel iso,
SqlInternalTransaction internalTransaction)
at System.Data.SqlClient.SqlDelegatedTransaction.Promote()

System.Transactions.TransactionPromotionException: Failure while
attempting to promote transaction.
at System.Data.SqlClient.SqlDelegatedTransaction.Promote()
at
System.Transactions.TransactionStatePSPEOperation.PSPEPromote(InternalTransaction
tx)
at
System.Transactions.TransactionStateDelegatedBase.EnterState(InternalTransaction
tx)

System.Transactions.TransactionAbortedException: The transaction has
aborted.
at
System.Transactions.TransactionStateAborted.CheckForFinishedTransaction(InternalTransaction
tx)
at System.Transactions.EnlistableStates.Promote(InternalTransaction
tx)
at System.Transactions.Transaction.Promote()
at
System.Transactions.TransactionInterop.ConvertToOletxTransaction(Transaction
transaction)
at System.Transactions.TransactionInterop.GetExportCookie(Transaction
transaction, Byte[] whereabouts)
at
System.Data.SqlClient.SqlInternalConnection.EnlistNonNull(Transaction
tx)
at System.Data.SqlClient.SqlInternalConnection.Enlist(Transaction tx)
at System.Data.SqlClient.SqlInternalConnectionTds.Activate(Transaction
transaction)
at
System.Data.ProviderBase.DbConnectionInternal.ActivateConnection(Transaction
transaction)
at
System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection
owningConnection)
at
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at SqlServiceBrokerQueueTest.WAITFORBug() in
SqlServiceBrokerQueueTest.cs:line 375