User Guide > How to Work with... > Image Data Providers > SQL Image Data Providers > 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. |
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").
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. |
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:
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:
SQL |
Copy Code |
---|---|
SqlCmd -S .\SQLEXPRESS |
SQL |
Copy Code |
---|---|
EXEC sp_configure 'show advanced options', '1' GO RECONFIGURE GO EXEC sp_configure 'clr enabled', 1 GO RECONFIGURE GO |
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 |