CODEWITHSUNDEEP
sql-servert-sqltransactions
witpo
witpo

Reputation: 465

Sql progress logging in transaction

I am would like to display progress feedback for user while stored procedure is running (multiple setps). All sql code is in transaction so I am using a service broker to get a real time updates. Could you suggest what is wrong with code below as I am not getting any messages in my queue?
Is there a better way of doing it?

--queue
create queue myQueue

--service
create service myLogs
on queue myQueue ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);

--log handler
create event notification myLogHandler
on  server
for userconfigurable_0
to service 'myLogs', 'current database' ;

--message
EXEC master..sp_trace_generateevent @event_class = 82, @userinfo = N'test msg'

---transaction test
begin transaction
EXEC master..sp_trace_generateevent @event_class = 82, @userinfo = N'tran test msg'
rollback transaction


--receive message
declare @message_body  xml;  
receive  top (1) @message_body = message_body
from myQueue
select @message_body

--display queue  
select * from myQueue

Upvotes: 3

Views: 1310

Answers (2)

Paul Spain
Paul Spain

Reputation: 549

An arguably simpler way would be to:

--------------------------------
--Worker process 
--------------------------------

BEGIN TRANSACTION

--While in your loop or at each stage of the proc you can do this
INSERT INTO MyLoggingTable VALUES('My Message')

COMMIT

--------------------------------
--Reporting process
--------------------------------

SELECT * FROM MyLoggingTable WITH (NOLOCK)

The NOLOCK lets you read the progress of the proc even though its logging to it inside the transaction.

NOTE that this allows you to read the data "LIVE" but if the worker process dose a rollback then the data will be removed.

You can get around that by also logging to a @MyTable, this will survive the rollback and so you can rollback and then copy the content of @MyTable into MyLoggingTable.

Upvotes: 3

Martin Smith
Martin Smith

Reputation: 453278

That works fine for me though I needed to wait a few moments and run the receive from queue bit again before I saw anything.

Perhaps you need to ENABLE_BROKER on the database.

ALTER DATABASE YourDB SET  ENABLE_BROKER WITH  ROLLBACK IMMEDIATE

(Warning: Will kill existing connections to the DB)

Upvotes: 1

Related Questions