Transactional messaging set option

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.