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.
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" (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:
- Run the SqlCmd.exe command line utility.
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.