0

I am using the Service Broker on SQL Server 2008 R2, and I am registering a stored procedure that has multiple select statements like this:

SELECT
     A.Date
     ,A.Id
FROM
    dbo.Appointment A

SELECT
    P.Name
FROM
    dbo.Person P

When I register the query using the C# object SqlDependency OR SqlNotificationRequest, I get one entry in the sys.conversation_endpoints table for each select statement in my query. So with the above query, I am getting 2 endpoints added. When I insert a row into the dbo.Appointment table I get a single notification delivered to the endpoint related to the first query. I then call END CONVERSATION on the @conversation_handle, but since I registered this as a stored procedure I want to end the conversation of the second query as well. I am not sure how to do this, as both entries have different conversation_group_id columns.

Is there a way to do this using SqlDependency/SqlNotificationRequest, or do I need to setup the conversation groups manually in my stored procedure and stop using SqlDependency/SqlNotificationRequest?

lehn0058
  • 19,977
  • 15
  • 69
  • 109
  • I suppose I could set a timeout (15 min or so) for the notification which should end all conversations if they have not fired yet, though I would like to clean these up right away. – lehn0058 Jul 17 '13 at 15:39

1 Answers1

3

After much searching, I have determined the only way to clean up query notifications properly is by setting a timeout on them and filtering events that have already been handled at the application layer.

lehn0058
  • 19,977
  • 15
  • 69
  • 109