I was running into this issue as well. You need to create a new SqlDependency entity (after unsubscribing the existing one from the OnChange event) and then run a new ExecuteReader command. I got the idea from this post: http://www.codeproject.com/Articles/12335/Using-SqlDependency-for-data-change-events This usually makes sense, as once you have been notified of a change you will … Read more
Since multiple SqlDependency instances are being created, I would check for Duplicate Subscriptions, as outlined here. The subscriptions may be considered duplicates depending on your configuration. The behavior “means that if a notification is requested for identical queries, only one notification is sent.” Another thing to check is whether the OnChange event could actually be … Read more
1) Yes, I consider it reliable as in it does correctly the purpose was designed to do (cache invalidation) 2) No. This is why you can only subscribe by issuing a query, this ensures that there is no race between the fetching of the data and new updates notifying 3) Database (or instance) restart signals … Read more
The most complete list I can find (from here) is as follows: The projected columns in the SELECT statement must be explicitly stated, and table names must be qualified with two-part names. Notice that this means that all tables referenced in the statement must be in the same database. The statement may not use the … Read more