SqlDependency query join same table twice

Question

Hi,

I've just started to use the Service Broker to update my VB.net application using the SqlDependency class.  This appears to be the easy route to using Notifications as opposed to the more involved SqlNotificationRequest class but I'm hoping SqlDependency will help me achieve what I am trying to do.

I have it working with one query but with a second DataGridView I want to update using the following query:

SELECT t2.[Name] AS [Col1], t3.[Name] AS [Col2]
FROM dbo.[Table1] t1
INNER JOIN dbo.[Table2] t2 ON t1.[From Code]=t2.[Code]
INNER JOIN dbo.[Table2] t3 ON t1.[To Code]=t3.[Code]

If I remove the second INNER JOIN for t3 (and reference to t3.[Name] in SELECT) the SqlDependency works ok but otherwise it fails with an invalid statement error.

I have looked at the Creating a Query for Notification (http://msdn.microsoft.com/en-US/library/ms181122.aspx) article but can't see why this would no be allowed.  Can anyone suggest a workaround other than storing the [Name] in Table1?

Answer 1

Hi Tippers,

Thank you for your question.
I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated.
Thank you for your understanding and support.

TechNet Subscriber Support

If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.

---

Best Regards,
Iric
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 2

Hi,

You are correct that the statement is invalid. In profiler, there are query notification events. Specifically there is the QN:Subscription event. This is showing that the statement is invalid. Below is the textdata from this event with the query. You can see that the info shows invalid. Unfortunately it does not tell why the query notification code is marking it invalid.

<qnev:QNEvent xmlns:qnev="http://schemas.microsoft.com/SQL/Notifications/QueryNotificationProfiler"><qnev:EventText>subscription fired</qnev:EventText><qnev:SubscriptionID>0</qnev:SubscriptionID><qnev:NotificationMsg>?&lt;qn:QueryNotification xmlns:qn="http://schemas.microsoft.com/SQL/Notifications/QueryNotification" id="0" type="subscribe" source="statement" info="invalid" database_id="0" sid="0x61508A13FF0E0944928C320863501AD1"&gt;&lt;qn:Message&gt;cad890de-3455-47f7-bb35-668d4fe1811e;40d34b2e-e91a-4d1a-aab6-4af60f30d9fc&lt;/qn:Message&gt;&lt;/qn:QueryNotification&gt;</qnev:NotificationMsg><qnev:BrokerDlg>28D91514-651C-E211-9B71-001372290D6E</qnev:BrokerDlg></qnev:QNEvent>

I tried to rewrite but all the methods I came up with, like union, where also invalid for query notification. It is a guess but I would say that maybe it is considering it a self join but not positive because there is nothing in the product to tell us the why.

If you can come up with a valid query then probably denormalizing the data, like you suggest would be the way to go.

Bill -- Microsoft CTS.

Answer 3

Hi Bill,

Thanks for your reply.  I think I'll have to look at adding the fields I require to the base table rather than joining them.  It just requires a third party involvement to do so and I was hoping to find a workaround myself.

Thanks again.

Chris