Setting Up SQL Server 2005 and SQL Server 2008 Cache Dependencies

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.

The steps provided in this section for Visual Studio 2010 are also applicable for Visual Studio 2012.

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").

Close all instances of Visual Studio 2010.
Click Start Button and choose All Programs.
Select Visual Studio Tools.
Select Visual Studio 2010 Command Prompt.
Run the SqlCmd.exe command line utility specifying the command line parameter -S in the command window:
SQL
Copy Code
SqlCmd -S .\SQLEXPRESS
The above connects SqlExpress on the local machine.
The SqlCmd provides a command prompt. Enter the following SQL statements:
SQL
Copy Code
USE ImageStorage ALTER DATABASE ImageStorage SET ENABLE_BROKER; GO
QUIT or EXIT.

SQL	Copy Code
SqlCmd -S .\SQLEXPRESS

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

If you have any Visual Studio instance with Database View window open then the command Set Enable_Broker will appear not to execute. It will appear to be hung/frozen.

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" (http://msdn.microsoft.com/en-us/library/9dz445ks(VS.80).aspx). 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:

Run the SqlCmd.exe command line utility.
SQL
Copy Code
SqlCmd -S .\SQLEXPRESS

SQL	Copy Code
SqlCmd -S .\SQLEXPRESS

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

Enter QUIT or EXIT.

For more detailed explanation and usage of the SQL cache dependency refer to the article on the ASP.NET website: http://www.asp.net/learn/data-access/tutorial-61-vb.aspx