Sqlcachedependency not invalidating
For the website to be able to receive notifications, a listener must be created. I decided to wrap the code up into an Extension Method. Add a new Class file to the application and name it My Extensions.
Extension methods are static methods, so you must mark the class as static.
In order for SQL Server to send notifications, you must first enable Microsoft SQL Server 2005 Service Broker.
You can check to see whether the Service Broker is enabled on your server by executing the following SQL syntax: Finally you must inform SQL Server that the user running Internet Information Services (IIS) has permission to subscribe to query notifications.
NET 2.0 using C#, and running against the Adventure Works database. This login (or the login used for impersonation) should be granted all the permissions that I list in the Granting permissions section of this article.
You can define your connection string in your web.config file, as follows: function will be called.
The biggest factor here is that the website does not have to continually poll the database.
This provides a much more granular level of control and takes any guesswork out of the question of how often the cache should be refreshed.
This is possible via a new feature called query notification, used in conjunction with . The aim of this article is to show you how to make full use of SQL 2005 query notification with ASP. It tackles the implementation details largely from SQL Server’s perspective and includes many DBA tips and tricks for troubleshooting query notification.
All this is done without the need to write any complex application code.
To demonstrate the ease of use of this feature, I’m going to showcase a simple web application written in ASP. The first step is to define, in your connection string, the identity of the Windows login that will be executing the queries against SQL Server.
However, this unnecessarily increases network round-trips, and is an inefficient use of resources.