Generating an event from an Activation Stored Proc

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?

Generating an event from an Activation Stored Proc

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?

Generate a GUID in MS SQL (t/SQL)

Hello,
I'm writing a MS SQL script to roll-up an Event List in Sharepoint, and
I have it working except I haven't found a way to generate a GUID using
t/SQL in MS SQL. Is there a function or script or something I can use
to generate a GUID value? I can do this in ASP or ColdFusion or a few
other ways, but keeping this totally in MS SQL as a stored procedure I
can schedule to run periodically would be the simplest.
Thanks for any suggestions or ideas on this.
Also, from a Sharepoint standpoint, SPS nor WSS supports roll-ups for
Events or any list, which is why I'm writing this on my own. Also
there are third party plugins, but most are around $1600 or more, which
is crazy since this can be done in MS SQL if I can only figure out the
GUID bit.
Thanks for any help or assistance --
SamSam
Try newid(), I don't have access to the query analyzer tonight, but
from memory it should a litlte like:
<sql snippet>
DECLARE @.MyUUID AS uniqueidentifier
SET @.MyUUID = newid()
SELECT @.MyUUID
</sql:snippet>
HTH,
Duncan.|||Have you tried this method?
SELECT NEWID()
Keith Kratochvil
"Alex" <samalex@.gmail.com> wrote in message
news:1141685000.839522.40640@.i39g2000cwa.googlegroups.com...
> Hello,
> I'm writing a MS SQL script to roll-up an Event List in Sharepoint, and
> I have it working except I haven't found a way to generate a GUID using
> t/SQL in MS SQL. Is there a function or script or something I can use
> to generate a GUID value? I can do this in ASP or ColdFusion or a few
> other ways, but keeping this totally in MS SQL as a stored procedure I
> can schedule to run periodically would be the simplest.
> Thanks for any suggestions or ideas on this.
> Also, from a Sharepoint standpoint, SPS nor WSS supports roll-ups for
> Events or any list, which is why I'm writing this on my own. Also
> there are third party plugins, but most are around $1600 or more, which
> is crazy since this can be done in MS SQL if I can only figure out the
> GUID bit.
> Thanks for any help or assistance --
> Sam
>