Transactional behavior is controlled by the set transactional messaging command, which provides three modes of operation, allowing you to select preferred behavior when you use messaging functions in a transaction:
set transactional messaging [ none | simple | full]
none – provides that messaging operations and database operations do not affect each other. In this example, msgsend is executed and the message is sent to the message bus, whether insert succeeds or fails:
begin tran msgsend (...) insert (...) rollback
simple (the default setting) – causes database operations to affect messaging operations, but messaging operations do not affect the database transaction. In this example, insert is not aborted if msgsend fails:
begin tran insert (...) msgsend (...) commit
In this example, msgsend is rolled back:
begin tran insert (...) msgsend (...) rollback
full – provides full transactional behavior. In this mode, messaging operations and database operations affect each other. If the messaging operation fails, the transaction rolls back. If database transactions fail, messaging operations roll back.
begin tran select @message=msgrecv(Q1,...) insert t2 values (@message,...) select msgsend ( t2.status,...) commit tran
When transactional messaging is set to full or simple, uncommitted transactions that send or publish messages cannot be read within the same transaction.
Transact-SQL applications can specify a preferred mode, depending on their application requirements.
You cannot use set transactional messaging inside
a transaction.