sql server - Service Broker messages inside a transaction -


i writing stored procedure perform following operations:

  1. begin transaction
  2. perform tasks
  3. use service broker kick-off background process
  4. wait response message (with job status) service broker
  5. commit or rollback transaction, depending on response message

the issue service broker communication not working inside transaction:

  • the message queue has activation enabled, associated stored procedure not executed (print statements in stored procedure not written errorlog file)
  • receive command times out

here's extract of code:

-- comment out following line make work begin tran t1  declare @update_msg xml([sb].[service_broker_xxx_schemas]) = ' <request xmlns="xxx">   <table xmlns="xxx">     <fields>       xxx     </fields>   </table>   <requested_by>xxx</requested_by> </request>'  declare @conversation_handle uniqueidentifier             ,@message_body varbinary(max)             ,@message_type_name nvarchar(256)             ,@timestamp datetime2          begin dialog conversation @conversation_handle         service [xxx_initiating_service]         service 'xxx_target_service'         on contract xxx_contract         encryption = off;          send on conversation @conversation_handle         message type [xxx_command](@update_msg);   select * sys.transmission_queue with(nolock) --print @conversation_handle  waitfor (     -- handle 1 message @ time     receive top(1)   @conversation_handle = conversation_handle     -- identifier of dialog message received on                     ,@message_type_name = message_type_name                     ,@message_body=message_body                     -- message contents                     ,@timestamp = getdate()                     [sb].[xxx_initiator_queue]                     conversation_handle = @conversation_handle ), timeout 1000  -- if queue empty 1 second, give update , go away if @@rowcount > 0 begin         select @@rowcount, @message_type_name, convert(xml, @message_body)         end conversation @conversation_handle; end else begin     print 'did not receive response service broker.' end  -- comment out following line make work commit tran t1 

what correct way implement service broker messaging inside transaction?

sending messages via service broker transactional. is, if begin tran; send;, message isn't sent until commit.


Comments

Popular posts from this blog

javascript - Thinglink image not visible until browser resize -

firebird - Error "invalid transaction handle (expecting explicit transaction start)" executing script from Delphi -

mongodb - How to keep track of users making Stripe Payments -