Change Notification with Sql Server 2008

by

Notification Services was deprecated, but you don’t want to use that anyway.

You might consider Service Broker messages in some scenarios; the details depend on your app.

In most cases, you can probably use SqlDependency or SqlCacheDependency. The way they work is that you include a SqlDependency object with your query when you issue it. The query can be a single SELECT or a complex group of commands in a stored procedure.

Sometime later, if another web server or user or web page makes a change to the DB that might cause the results of the previous query to change, then SQL Server will send a notification to all servers that have registered SqlDependency objects. You can either register code to run when those events arrive, or the event can simply clear an entry in the Cache.

Although you need to enable Service Broker to use SqlDependency, you don’t need to interact with it explicitly. However, you can also use it as an alternative mechanism; think of it more as a persistent messaging system that guarantees message order and once-only delivery.

The details of how to use these systems are a bit long for a forum post. You can either Google for them, or I also provide examples in my book (Ultra-Fast ASP.NET).

More Related Contents:

  1. Using merge..output to get mapping between source.id and target.id
  2. SQL Server, How to set auto increment after creating a table without data loss?
  3. How can I truncate a datetime in SQL Server?
  4. Errors: “INSERT EXEC statement cannot be nested.” and “Cannot use the ROLLBACK statement within an INSERT-EXEC statement.” How to solve this?
  5. How to add Active Directory user group as login in SQL Server
  6. Why use the INCLUDE clause when creating an index?
  7. How to set a default value for an existing column
  8. How to drop column with constraint?
  9. SQL Server: combining multiple rows into one row
  10. SQL Server Query: Fast with Literal but Slow with Variable
  11. How to add xml encoding to xml Output in SQL Server
  12. How do I find a stored procedure containing ?
  13. SQL Server GUID sort algorithm. Why?
  14. An attempt to login using SQL authentication failed
  15. SQL Server, the misleading XLOCK & optimizations
  16. How to create the linked server for SQL Server 2008 where we have the database from 2000 and 2005
  17. How to find SQL Server running port?
  18. SQL Server 2008 – How do i return a User-Defined Table Type from a Table-Valued Function?
  19. Changing SQL Server collation to case insensitive from case sensitive?
  20. Unable to login to SQL Server + SQL Server Authentication + Error: 18456
  21. What’s the best way to store co-ordinates (longitude/latitude, from Google Maps) in SQL Server?
  22. Database design: Calculating the Account Balance
  23. Instead of trigger in SQL Server loses SCOPE_IDENTITY?
  24. INFORMATION_SCHEMA vs sysobjects
  25. Merge multiple rows into a single row
  26. In SQL Server, how can I find everywhere a column is referenced?
  27. ‘Incorrect SET Options’ Error When Building Database Project
  28. Using Full-Text Search in SQL Server 2008 across multiple tables, columns
  29. Why insert-select to variable table from XML variable so slow?
  30. Generate MD5 hash string with T-SQL

Leave a Comment