SQL Server 2005 and SQL Server 2008 have their notification infrastructure built into the database with a messaging system called the "Service Broker". The Service Broker manages queues. These queues are database objects at the same level as the stored procedures, tables and views.

Enabling the Service Broker in SQL Server Express

SQL Server 2005 and SQL Server 2008 are usually locked down for security reasons. The SQL Server broker must be enabled for each specific database that you want to use for cache dependencies. In order to enable the service broker, you must first launch the Visual Studio 2010 command prompt window.

The following are the steps to do that: (The example uses SQL Express:2005 and the default database table "ImageStorage").

1. Close all instances of Visual Studio 2010.
2. Click Start Button and choose All Programs.
3. Select Visual Studio Tools.
4. Select Visual Studio 2010 Command Prompt.
5. Run the SqlCmd.exe command line utility specifying the command line parameter -S in the command window:

   SQL	Copy Code
   SqlCmd -S .\SQLEXPRESS

6. The above connects SqlExpress on the local machine.
7. The SqlCmd provides a command prompt. Enter the following SQL statements:

   SQL	Copy Code
   USE ImageStorage ALTER DATABASE ImageStorage SET ENABLE_BROKER; GO

8. QUIT or EXIT.

SqlDependency Rules

There are a number of rules that must be followed for SqlDependency to work correctly. These rules are documented in Microsoft msdn library article titled "Using SqlDependency in an ASP.NET Application" (https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/sqldependency-in-an-aspnet-app). Here is a list of some to bear in mind:

• You must not use SELECT * in your query. Use individual column names.
• You must use fully qualified name of the table, example: dbo.ImageStore
• All tables referenced in the statement must be in the same database.
• The statement may not use unnamed columns or duplicate column names.
• The statement must not reference tables with computed columns.
• The projected columns in the SELECT statement may not contain aggregate expressions unless the statement uses a GROUP BY expression. When a GROUP BY expression is provided, the select list may contain the aggregate functions COUNT_BIG() or SUM(). However, SUM() may not be specified for a nullable column. The statement may not specify HAVING, CUBE, or ROLLUP.

The procedure that sends notifications is a .NET procedure. Make sure your Sql server has CLR support enabled.

Here is how it can be done in SQLEXPRESS:

1. Run the SqlCmd.exe command line utility.
   

   SQL	Copy Code
   SqlCmd -S .\SQLEXPRESS

2. Enter the following SQL statements:
   

   SQL	Copy Code
   EXEC sp_configure 'show advanced options', '1' GO RECONFIGURE GO EXEC sp_configure 'clr enabled', 1 GO RECONFIGURE GO

3. Enter QUIT or EXIT.