Sending and Receiving SQL Server Service Broker Messages within Nested Transactions

I'd like to use SQL Server 2008 Service Broker to log the progress of a long-running (up to about 30 minu开发者_如何学运维tes) transaction that is dynamically created by a stored procedure. I have two goals:

1) To get real-time logging of the dynamically-created statements that make up the transaction so that the progress of the transaction can be monitored remotely,

2) To be able to review the steps that made up the transaction up to a point where a failure may have occurred requiring a rollback.

I cannot simply PRINT (or RAISERROR(msg,0,0)) to the console because I want to log the progress messages to a table (and have that log remain even if the stored procedure rollsback).

But my understanding is that messages cannot be received from the queue until the sending thread commits (the outer transaction). Is this true? If so, what options do I have?

---

It is true that you cannot read messages from the service queue until the transaction is committed.

You could try some other methods:

1. use a sql clr procedure to send a .net remoting message to a .net app that receives the messages and them log them.

2. use a sql clr procedure to write a text or other log file to disk.

3. Some other method...

Regards

AJ