I am trying to raise an event using sp_trace_generateevent in my activation stored proc. (dbo.ActivationSP)
EXEC sp_trace_generateevent @.event_class = 82, @.userinfo = N'Test Event'
There is a Service broker service listening to this event.
The problem is the event is getting fired whenever the activation SP is executed (could see in profiler) but the secondtargetqueue doesnt receive any messaages.
But if manually do a "EXEC sp_trace_generateevent", the secondtargetqueue receives a messaage.
Both the queues and sevices are in the same database.
The following are the code snippets
-- Code for queue on which the activation is working
CREATE QUEUE TargetQueue WITH STATUS=ON, ACTIVATION (PROCEDURE_NAME = dbo.ActivationSP,MAX_QUEUE_READERS = 5,Execute AS 'dbo') ;
Create Service ReceiverService ON QUEUE TargetQueue (SampleContract)
-- Code for Queue listening to event
Create Queue SecondTargetQueue WITH status= ON
Create Service SecondReceiverService ON QUEUE SecondTargetQueue ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification])
CREATE EVENT NOTIFICATION TestNotification
ON SERVER FOR UserConfigurable_0 TO SERVICE 'SecondReceiverService', 'current database'
Due to a product defect some profiler events are suppresed under activation. A possible workaround is to use a CLR activated procedure that connects back to the server (using ADO.Net and a real connection, not the context connection) to execute the code you need.
HTH,
~ Remus
Thanks Remus
Can you help me with some example links?
