Click here to monitor SSC
  • Av rating:
  • Total votes: 128
  • Total comments: 50
Sanchan Sahai Saxena

Using and Monitoring SQL 2005 Query Notification

11 August 2006

With the advent of SQL Server 2005, your application can request SQL Server to notify it when critical data in a database has changed. Up to that point, an application can safely continue to retrieve data from the cache. This provides a much more granular level of control and takes any guesswork out of the question of how often the cache should be refreshed. This is possible via a new feature called query notification, used in conjunction with .NET 2.0.

The aim of this article is to show you how to make full use of SQL 2005 query notification with ASP.NET 2.0. It tackles the implementation details largely from SQL Server's perspective and includes many DBA tips and tricks for troubleshooting query notification. However, the article should also interest developers as it will enable them to implement QN in their ASP.NET applications, without causing performance degradation of their SQL Server machines.

An overview of query notification

A multi-user web application should be able to provide the latest critical data to its users. To be sure of meeting this need, your application may have to retrieve the results from a database every time the user issues the query. However, this unnecessarily increases network round-trips, and is an inefficient use of resources. To reduce the number of round-trips, developers have traditionally relied on caching mechanisms. Once a query is run, the data is subsequently retrieved from the application's local cache. However, the lifetime value of this cache is generally defined so that the application can periodically update the cache with new values from the database, irrespective of whether the data in the back end has changed or not. This is much better, but it is still not precise.

With query notification enabled then when you run a query against the back-end database, you not only retrieve the data into the cache, but also tell SQL Server to register a subscription for notification if the underlying data changes in a way that will affect the result of the query.

TIP
The change detection mechanism is based on that used for indexed views. In the same way that only views that obey certain criteria can be indexed, so there are certain restrictions applying to the SELECT statements that can be used with query notifications. See http://msdn2.microsoft.com/en-US/library/ms181122.aspx

In turn, SQL Server cooperates with the application. Whenever it runs a statement that causes the underlying data to change, SQL Server looks for any active subscriptions in the sys.dm_qn_subscriptions Dynamic Management View (DMV) and sends a notification message to a message queue on Service Broker called QueryNotificationErrorsQueue.

When the notification is received, the event handler in your application invalidates the cache and the next time the application runs the query, it will fetch the data from the back-end server. All this is done without the need to write any complex application code.

NOTE: Query notification is solely dependent on Service Broker and doesn’t rely on Notification Services.

Enabling query notification in ASP.NET using SQLCacheDependency

To demonstrate the ease of use of this feature, I'm going to showcase a simple web application written in ASP.NET 2.0 using C#, and running against the AdventureWorks database.

Let's start by creating a new ASP.NET 2.0 web application. The first step is to define, in your connection string, the identity of the Windows login that will be executing the queries against SQL Server. This login (or the login used for impersonation) should be granted all the permissions that I list in the Granting permissions section of this article.

You can define your connection string in your web.config file, as follows:

<connectionStrings>

<add name="conAW"
connectionString = "server = <servername>; integrated security = true;
database=AdventureWorks; persist security info=true"
providerName="system.data.sqlclient"/>

</connectionStrings>

The default.aspx.cs file (supplied in the code download for this article) is very simple, but describes fully how to take advantage of query notification using the SQLCacheDependency class. A SQLCacheDependency object can be used to establish a relationship between an item stored in the Cache object and the results of a SQL Server 2005 query.

First, in the GetCustomerStores() function, I instantiate an object of type SQLCacheDependency, passing in the ADO.NET command object that is used to submit the query:

System.Web.Caching.SqlCacheDependency new_dependency =
   new System.Web.Caching.SqlCacheDependency(command);

Second, I populate the cache with the results returned by executing the query, using the Cache.Insert method. We specify the cache key (CustomerStores), the object to be inserted (a DataSet, Customer_Stores, containing the results of our query on the Store table in AdventureWorks), and finally establish the link between Customer_Stores and SQLCacheDpendency:

Cache.Insert("CustomerStores", Customer_Stores, new_dependency);

The first time this application loads, there will be nothing in the application cache and Customer_Stores will be null. As a result, the GetCustomerStores function will be called. When this happens, the function will register a notification subscription with SQL Server. It will also load up the cache with the output of our query. When the user runs this query again the data will be retrieved from the cache. You can confirm this by running the app in debug mode.

Let's also take a look at the Global.asax file (also included in the code download). Here, I am calling the System.Data.SqlClient.SqlDependency.Start() method:

void Application_Start(object sender, EventArgs e)
{
    string connectionString =
System.Configuration.ConfigurationManager.
ConnectionStrings["ConAW"].ConnectionString;
      System.Data.SqlClient.SqlDependency.Start(connectionString);

This creates the necessary queue, service and procedure and starts a listener to monitor the queue. You don't have to write a single line of code to implement this – it is all done automatically. When SQL Server raises a notification it merely sends it to QueryNotificationErrorsQueue. Once it arrives there, the above listener gets activated and retrieves the message from the queue. SQL Server doesn't send the notification to the machine running the application.

 Enabling query notification in SQL Server

The code above demonstrates how little you need to do in your front-end application to make good use of query notifications. However, there are certain steps that need to be carried out on the server-side in order for this to work, the first being to enable Service Broker.

Enabling Service Broker

As I mentioned earlier, query notification relies on the underlying Service Broker architecture. So the first step is to enable Service Broker on the database in question:

use master
alter database <dbname> SET ENABLE_BROKER

TIP
To verify that the broker is enabled, run the query, select is_broker_enabled from sys.databases where name = '<dbname>' and make sure it is set to 1 for the database in question.

Granting permissions

Before query notification can work you need to grant the following permissions to the database_principal. This database_principal can be the same login as you used in your connection string:

  • CREATE PROCEDURE, QUEUE, and SERVICE permissions
  • SUBSCRIBE QUERY NOTIFICATIONS
  • SELECT on underlying tables
  • RECEIVE on QueryNotificationErrorsQueue

The database_principal can be given permission to create the queue, the service and the procedure using the following T-SQL commands:

use <dbname>
GRANT CREATE PROCEDURE TO <database_principal>
GRANT CREATE QUEUE TO <database_principal>
GRANT CREATE SERVICE TO <database_principal>

The next step is to grant the requisite privileges to the login to subscribe to query notifications in your database:

use <dbname>
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO database_principal

Make sure that the database_principal listed above is either the login specified in the connection string of your application, or the login that is impersonated in your application to run queries against the database. In this article, I am assuming that there is no impersonation.

In order to register a subscription for QN, the database_principal must have SELECT privileges on the underlying tables that it is trying to query. This would be the case if the login is sysadmin or db_owner. If not, then run the following command to grant the SELECT privileges to the database_principal:

use <dbname>
GRANT SELECT ON OBJECT::schema.tablename TO database_principal

The database_principal must also have requisite permissions to receive the notification messages sent to the message queue by SQL Server. The login above should therefore also have RECEIVE permissions on the QueryNotificationErrorsQueue. You can grant this permission as follows:

Use <dbname>
GRANT RECEIVE ON QueryNotificationErrorsQueue TO <database_principal>

TIP
You can run exec sp_helprotect NULL, 'database_principal' in the context of your database to see if any of the above permissions are missing. Generally, if these requisite permissions are not granted, query notification will not work.

Finally, in cases where the T-SQL SELECT that you are registering for query notification queries more than one database you should set the TRUSTWORTHY property of each database to ON:

ALTER DATABASE db1 SET TRUSTWORTHY ON
ALTER DATABASE db2 SET TRUSTWORTHY ON

Once you have granted the above permissions and set your front-end app to use SQLCacheDependency, you are ready to take advantage of query notification.

Query notification in action

When your front-end app runs the T-SQL query for the first time under the context of database_principal, SQL Server will register a notification request, create a subscription in the DMV sys.dm_qn_subscriptions and then execute the command.

TIP
You will always see an entry in this DMV immediately after the front-end app runs the query with the subscription for the first time. You can also run SQL Profiler and monitor QN:Subscription (1 – Subscription registered). This event is raised every time a subscription is registered successfully by the database engine.

If SQL Server is not able to register a subscription, it will send a <qn:QueryNotification> message to the queue. If the Type of this message is set to subscribe, it indicates that SQL Server was not able to successfully register a subscription. Refer to the Monitoring and troubleshooting query notification section below for more information on this.

Once this subscription is registered and the results returned to the app by the database engine, the front-end app will continue to query the cache, until a notification message is raised by SQL Server and sent to the QueryNotificationErrorsQueue, indicating that a change has occurred that may affect the result of the subscribed query.

Using SQLCacheDependency, your application has already established the relationship between an item in your application's cache and the results of this query. When the message arrives in QueryNotificationErrorsQueue, the listener will raise the OnChange event in your application.

The associated event handler (OnChangeEventHandler) will handle this event. This delegate has a parameter called SQLNotificationEventArgs, which captures the details of the message coming from SQL Server. The cache will be invalidated and, next time the application runs, it will fetch the data directly from SQL Server (the GetCustomerStores() function will get called again after the notification is received).

The notification messages sent to the queue are in XML and are of the <qn:QueryNotification> variety. The Type element is set to the value change, indicating that this notification message was created because of a change that could affect the outcome of the query.

A notification will be raised by SQL Server whenever the database engine cannot guarantee that the data in the cache is up to date. Scenarios include:

  • Changes to the data in the columns since the last SELECT statement was run
  • SQL Server being restarted
  • Changes to the underlying schema
  • Expiration of a subscription (see later)

The Source and Info elements of the notification message provide the details about the kind of change that triggered this notification. Refer to the Monitoring and troubleshooting Query Notification section below, for more information on this.

This subscription has a default timeout value, which is reflected in the timeout column in the sys.dm_qn_subscriptions DMV. If the data doesn't change during this time, SQL Server will raise a notification at the end of the subscription timeout, and will remove this subscription from the DMV.

TIP
If a duplicate subscription is submitted before the timeout value of the existing subscription is reached, then SQL Server will update the timeout for the existing subscription and will not create a new subscription.

Monitoring and troubleshooting query notification

SQL Server provides a means of monitoring the query notification setup and its impact on overall server performance. The best way to monitor and troubleshoot query notification issues is by running a profiler trace. Create a new profiler trace which includes at least the following events:

  • Broker --> All
  • Query notifications -->All
  • Stored procedure --> RPC:Completed and Starting; SP:Starting and Completed; SP:StmtStarting and StmtCompleted
  • T-SQL events --> SQL:BatchStarting and BatchCompleted

Run the profiler and launch your front-end application. When the T-SQL query in your front-end application is run for the first time, you should see the QN:Subscription EventClass raised in profiler, indicating that the subscription was successfully registered by SQL Server (1 – Subscription registered). The TextData column in profiler should show text similar to the one below (id_num is the subscriptionId of this subscription):

<qnev:QNEvent xmlns:qnev="http://schemas.microsoft.com/SQL/Notifications/
                                  QueryNotificationProfiler">
<qnev:EventText>subscription registered
</qnev:EventText><qnev:SubscriptionID>id_num
                       
</qnev:SubscriptionID></qnev:QNEvent>

Also, a subscription will be registered in sys.dm_qn_subscriptions DMV at this stage.

TIP
Whenever SQL Server fires a notification, you will observe the event QN:Subscription (3 – subscription fired) in your profiler trace. If the notification cannot be fired due to issues with Service Broker, then you will see QN:Subscription (4 – Firing failed with broker error, 5 – Firing failed without broker error, 6 – Broker error intercepted).

If, on the other hand, SQL Server was not able to register a subscription, it will immediately raise a notification and you will see QN:Subscription EventClass (3 – Subscription Fired), but the TextData field will be similar to that shown below (note that I have removed unnecessary text and added line breaks for readability):

<qnev:QNEvent xmlns:qnev="http://schemas.microsoft.com/SQL/Notifications/
                                  QueryNotificationProfiler">

<qnev:EventText>

subscription fired

</qnev:EventText>

<qnev:SubscriptionID>0</qnev:SubscriptionID>

<qnev:NotificationMsg>

qn:QueryNotification

xmlns:qn="http://schemas.microsoft.com/SQL/Notifications/
                                 QueryNotification" id="0"

type="subscribe" source="statement" info="invalid"

database_id="<id>"

---------------------------more

</qnev:NotificationMsg>

---------------------------more

The important things to notice here are the Type and the Source elements. If the Type = subscribe, then it means that the subscription was not registered by SQL Server. To find out why SQL Server didn't register this subscription, you need to look at the Source and Info elements. For example:

  • Source = statement and Info = query indicates that SQL Server was not able to register a subscription because the SELECT statement didn't meet the requirements for query notification.
  • Source = system and Info = invalid indicates that SQL Server was not able to register a subscription because an invalid statement was issued (such as INSERT or UPDATE) which doesn't support notification.

Refer to the Query Notification Messages section in Books Online for more details on the description of each element. You can also refer to the SQL Server errorlogs to check for any error messages.

If the subscription registered successfully, then you should expect to receive a notification from SQL Server when the underlying data changes. To see this in action, update the Name of one of the stores in the Sales.Stores table in AdventureWorks. This will trigger a notification to the QueryNotificationErrorsQueue.

In Profiler, you will see QN:Subscription with an EventSubclass of 3 (Subscription fired). The TextData field will be similar to the following (again, I have removed unnecessary text and added line breaks for readability):

<qnev:QNEvent xmlns:qnev="http://schemas.microsoft.com/SQL/Notifications/
                                  QueryNotificationProfiler">

<qnev:EventText>subscription fired</qnev:EventText>

<qnev:SubscriptionID>subscription_id</qnev:SubscriptionID>

<qnev:NotificationMsg>qn:QueryNotification

xmlns:qn="http://schemas.microsoft.com/SQL/Notifications/
                                  QueryNotification" id="subscription_id"

type="change" source="data" info="update" database_id="<db_id>"

-----------------------------more

</qnev:NotificationMsg>

-----------------------------more

This time, you should see Type = change with the Source and Info elements indicating the nature of the change that caused the notification. Here, we have Source = data and Info = update, indicating that the notification was raised because an UPDATE statement was issued against one of the underlying tables in the SELECT statement.

If you see Source = system and Info = restart, then this  indicates that the notification was raised because SQL Server was restarted. Refer to Query Notification Messages topic in Books Online for full details on these events and their meanings.

If you don't see this event, SQL Server was not able to fire a subscription. In this case, you should refer to the profiler for any exceptions, attentions or error messages. Also, refer to SQL Server error logs for any errors pointed out. The information there should be sufficient to troubleshoot the issue.

You should check the QN:Subscription event  in the profiler trace for LoginName (which indicates the login that actually ran the query) and SessionLoginName (which indicates the login that was used by the application to connect to SQL Server in connection string) columns. The important thing to check here is that the LoginName value is the database_principal that is running all the queries in SQL Server.

When to use query notification

Whenever it cannot guarantee that the data in the cache is valid, SQL Server will raise a notification. Although this sounds like a very easy implementation, do remember that SQL Server's overall performance may suffer if you are not prudent about using query notification. After a subscription is registered, SQL Server has an additional responsibility to continuously monitor changes. As a result, any changes (updates, deletes or inserts) to the table are more expensive.

Whenever a DML query is run on the underlying table, SQL Server not only has to update the data in the table, it also has to check for any active subscriptions, and raise a notification accordingly. It is, therefore, important to maintain a balance between query notification and overall performance.

As you can see, not all scenarios benefit from query notifications. Ideally, a scenario in which the application primarily reads the data more often and updates are infrequent can benefit most from query notifications. For example, e-commerce websites using read-mostly lookup tables to showcase product catalogs will benefit from query notification, while online stock trading applications will not.

In conclusion

Query notification is just one of the great features that SQL Server 2005 provides. However, I can't do justice to a description of all its details without sounding pedantic. Let me know if you would like to more on this topic and I will be glad to publish it.

Thanks for your time reading this article. Check back for updates at my blog.

Sanchan Sahai Saxena

Author profile:

Sanchan works for Microsoft Corp in the US and has been with them for the past 3 years. He specializes in SQL Server and .NET. He is also active in the blog world (http://blogs.msdn.com/sanchan/). While not working on technology, he enjoys watching (at times playing too) cricket and football.

Search for other articles by Sanchan Sahai Saxena

Rate this article:   Avg rating: from a total of 128 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: Good job!
Posted by: Adam Machanic (view profile)
Posted on: Monday, August 14, 2006 at 9:12 AM
Message: I really enjoyed this article.

Subject: Great One
Posted by: Anonymous (not signed in)
Posted on: Monday, August 14, 2006 at 10:06 AM
Message: This is some great stuff man

Subject: Must read for professionals
Posted by: Anonymous (not signed in)
Posted on: Monday, August 14, 2006 at 10:08 AM
Message: A great analysis from a matured head

Sam Disouza

Subject: Excellent Job Man !
Posted by: Anonymous (not signed in)
Posted on: Monday, August 14, 2006 at 11:28 AM
Message: Congratulations on having this published. It is a great one !

Subject: Nice article.
Posted by: Anonymous (not signed in)
Posted on: Monday, August 14, 2006 at 3:16 PM
Message: That's a really useful article, nicely written too.

Subject: Proud of you,brother!
Posted by: Anonymous (not signed in)
Posted on: Monday, August 14, 2006 at 10:52 PM
Message: This is an article written by a highly intelligent and a mature-headed professional who knows his tools.Very informative and clear piece of art.

Subject: Great work
Posted by: Anonymous (not signed in)
Posted on: Wednesday, August 16, 2006 at 5:14 PM
Message: very informative and very intense. keep up the great work dude

Subject: How about for WinForms?
Posted by: Anonymous (not signed in)
Posted on: Wednesday, August 16, 2006 at 11:57 PM
Message: SqlCacheDependency is a System.Web class. Can it be used in WinForms without incurring much overhead, or is there a better way to get SQL notifications for WinForms apps?

Thanks,
Kevin

Subject: Great article!
Posted by: Anonymous (not signed in)
Posted on: Thursday, August 17, 2006 at 6:06 AM
Message: Excellent one.

Subject: RE:How about for WinForms?
Posted by: sanchans (view profile)
Posted on: Thursday, August 17, 2006 at 7:08 PM
Message: Hi Kevin,

You have two choices:

a) Use SQLDependency instead of SQLCacheDependency

b) OR you can get a little more into the specifics and get fine grain control by using SqlNotificationRequest.

If you have specific questions about them, please let me know and I will be glad to answer.

Sanchan

Subject: Its really great
Posted by: Anonymous (not signed in)
Posted on: Monday, August 28, 2006 at 3:11 AM
Message: Its really a great article regarding SQL Server 2005 Query Notification Services.

Subject: Creating a subscription in TSQL
Posted by: SimonSabin (view profile)
Posted on: Friday, September 15, 2006 at 8:18 AM
Message: Do you know why you cant create a subscription in TSQL. Its only Service Broker under the cover and the reading of the notification queue is possible in TSQL?

Subject: RE : Creating a subscription in TSQL
Posted by: sanchans (view profile)
Posted on: Monday, September 18, 2006 at 2:58 PM
Message: Well, the T-SQL commands (WAITFOR) and the underlying Service Broker infrastructure are necessary but not sufficient conditions for making QN happen.

Essentially, for the message to be put in the service broker queue for you to read it using T-SQL, you need two things:

a) You need to tell SQL Server WHAT to monitor. SQL Server doesnt monitor ALL data changes, only the ones for which you request notification. This logic to monitor changes without causing signifcant performance overhead has been abstracted by Microsoft so as not to bother the developers with it.

b) Once SQL Server notices the change, it needs to put a message in service broker queue, which you can then read using T-SQL.

All this is transparently managed by exposing the interfaces in .NET (SQLCacheDependency, SQLDependency and SQLNotificationRequest) to the customers.

Other stuff like - checking for proper permissions, opening a connection to monitor queues etc. is also encapsulated.

Hope this helps!

Sanchan

Subject: Microsoft running a survey on Query Notification
Posted by: sanchans (view profile)
Posted on: Thursday, October 05, 2006 at 11:01 PM
Message: We are running a survey on Query Notification and would appreciate any feedback.

http://blogs.msdn.com/sqlblog/archive/2006/09/30/778143.aspx

Thanks
Sanchan Sahai Saxena

Subject: Excellent
Posted by: Anonymous (not signed in)
Posted on: Tuesday, October 17, 2006 at 9:58 AM
Message: Wicked walkthough.

Subject: DTS in SQL Server 2005
Posted by: Anonymous (not signed in)
Posted on: Wednesday, October 25, 2006 at 1:12 PM
Message: Hi,
I am new in SQL DBA, i want to create DTS in SQL Server 2005.
Can you help me.
Thnx

Subject: SQL Express
Posted by: Anonymous (not signed in)
Posted on: Tuesday, January 16, 2007 at 9:34 AM
Message: For those of you using SQL 2005 Express Edition, the Query Notifications feature is not available.

Subject: Good One but not working in my case
Posted by: Anonymous (not signed in)
Posted on: Monday, February 19, 2007 at 7:05 AM
Message: I have followed all your instruction, but finding problem to make work.

My Problem : Can't able to Invalidate cache, on changing row in table.

My code is as under:
private ArrayList GetEmpDetails()
{
if (Cache["EmpDetails"] == null)
{

string strCon = System.Configuration.ConfigurationSettings.AppSettings["connStr"].ToString();

using (SqlConnection con = new SqlConnection(strCon))
{
SqlCommand cmd = new SqlCommand("dbo.[pr_Emp_SelectAll]", con);
con.Open();

IDataReader dr = cmd.ExecuteReader();
ArrayList objArr = new ArrayList();

while (dr.Read())
{
Employee objEmp = new Employee();
objEmp.Name = dr["EmpName"].ToString();
objEmp.EmpId = int.Parse(dr["EmpId"].ToString());
objEmp.Salary = double.Parse(dr["EmpSalary"].ToString());
objArr.Add(objEmp);
}

//Create Cache Dependency
SqlCacheDependency sqlDependencyEmp = new SqlCacheDependency(cmd);


//clsEmp objEmp = new clsEmp();
//objEmp.iEmpId = EmpId;
//DataTable dtEmpDetails = objEmp.SelectOne();

//Insert Cache
Cache.Insert("EmpDetails", objArr, sqlDependencyEmp);
}
return Cache["EmpDetails"] as ArrayList;
}
else
{
return Cache["EmpDetails"] as ArrayList;
}
}

Subject: Good One but not working in my case
Posted by: Anonymous (not signed in)
Posted on: Monday, February 19, 2007 at 7:06 AM
Message: I have followed all your instruction, but finding problem to make work.

My Problem : Can't able to Invalidate cache, on changing row in table.

My code is as under:
private ArrayList GetEmpDetails()
{
if (Cache["EmpDetails"] == null)
{

string strCon = System.Configuration.ConfigurationSettings.AppSettings["connStr"].ToString();

using (SqlConnection con = new SqlConnection(strCon))
{
SqlCommand cmd = new SqlCommand("dbo.[pr_Emp_SelectAll]", con);
con.Open();

IDataReader dr = cmd.ExecuteReader();
ArrayList objArr = new ArrayList();

while (dr.Read())
{
Employee objEmp = new Employee();
objEmp.Name = dr["EmpName"].ToString();
objEmp.EmpId = int.Parse(dr["EmpId"].ToString());
objEmp.Salary = double.Parse(dr["EmpSalary"].ToString());
objArr.Add(objEmp);
}

//Create Cache Dependency
SqlCacheDependency sqlDependencyEmp = new SqlCacheDependency(cmd);


//clsEmp objEmp = new clsEmp();
//objEmp.iEmpId = EmpId;
//DataTable dtEmpDetails = objEmp.SelectOne();

//Insert Cache
Cache.Insert("EmpDetails", objArr, sqlDependencyEmp);
}
return Cache["EmpDetails"] as ArrayList;
}
else
{
return Cache["EmpDetails"] as ArrayList;
}
}

Subject: Even after changing row, it takes data from cache.
Posted by: Anonymous (not signed in)
Posted on: Monday, February 19, 2007 at 7:08 AM
Message: With SqlCacheDepedency with SQL Server 2005, my understanding is we can Invalidate cache when the data in row of a particular table is changed... But it is not working in my case????

Web.config file code:

<appSettings>
<add key="connStr" value="server=192.168.0.28;database=SampleDatabase;uid=sa;pwd=ilink2006;"/>
</appSettings>
<connectionStrings>
<add name="ConnectionString"
connectionString="server=192.168.0.28;database=SampleDatabase;uid=sa;pwd=ilink2006;"/>
</connectionStrings>
<system.web>

<caching>
<sqlCacheDependency pollTime="1" enabled="true" >
<databases>
<add connectionStringName="ConnectionString" name="SampleDatabase"/>
</databases>
</sqlCacheDependency>
</caching>

Subject: In SQL Server
Posted by: Anonymous (not signed in)
Posted on: Monday, February 19, 2007 at 7:10 AM
Message: --Enable Broker Service
use SampleDatabse
alter database SampleDatabase set ENABLE_BROKER


--Grant Permission to User "dng"
use SampleDatabse
Grant Create Procedure to dng
Grant Create Queue to dng
Grant Create Service to dng
Grant SUBSCRIBE Query Notifications to dng
Grant receive on QueryNotificationErrorsQueue To dng

Alter Database SampleDatabase set trustworthy on

Subject: Thanks
Posted by: Anonymous (not signed in)
Posted on: Monday, February 19, 2007 at 7:11 AM
Message: I would appreciate your any kind of help.

Thanks for your Time and effort.

Subject: Drop me a line...
Posted by: Anonymous (not signed in)
Posted on: Saturday, March 03, 2007 at 5:09 PM
Message: I didnt get a chance to take a look at your comment, but do drop me a line at sanchan_saxena at hotmail dot com.

I will see how can I help you.

Subject: Will Query Notification work with SQL Express Edition...?
Posted by: Anonymous (not signed in)
Posted on: Friday, March 09, 2007 at 6:01 AM
Message: Could you please tell me, whether the Query notification feature works on SQL Express or not? I have trying to figure out for the past two days with no result whatsoever. Some say it's possible and some say it's not possible.

What is the truth?

Subject: Not possible wit sql express.
Posted by: Anonymous (not signed in)
Posted on: Sunday, March 18, 2007 at 4:34 PM
Message: Notificaiton services is not installed with sql express.
Therefore it's not possible.

Not only is it not possible, but who would even want to. There is no reason for it.
Sql express is client/server same machine data exchange. (used on satellite machines).

Subject: Your Example is not Complete
Posted by: Anonymous (not signed in)
Posted on: Monday, April 02, 2007 at 7:17 AM
Message: Sir, Your Example is not Complete. Lot of things missing.

Why we need to set many things for SQL Notification. It should be by default.

All SQL commands are not working

Subject: system.data.sqlclient.sqldependency.start
Posted by: Jimmy (view profile)
Posted on: Thursday, May 24, 2007 at 6:03 PM
Message: By placing system.data.sqlclient.sqldependency.start in the global.asax application_load, does that mean ALL QUERIES will register a notification in the broker service?

So for an examples sake, i have the start() code in my global class load method.
Now i execute a simple query like SELECT fname FROM dbo.person. I DO NOT create a sqlcachedependency for this sqlcommand. Will a notification be registered for service broker?

What i need to know is does all queries that run after starting the broker whether it registers a notification or not.

Subject: Enable Broker Query Wont Complete
Posted by: lance (not signed in)
Posted on: Monday, September 10, 2007 at 1:13 PM
Message: In SQL Server 2005 have a Notification Services folder visible in SQL Mgmt Studio. When I run the query:

use master
alter database bersinektron SET ENABLE_BROKER

It says, "Executing Query" but never finishes the query. Just keeps running. Ive let the query run for over an hour, but still it never finishes. Is there a known error when running this query and how would I fix it to get the query to run so that I can Enable_Broker? thanks.


Subject: Enable Broker Query Wont Complete
Posted by: lance (not signed in)
Posted on: Monday, September 10, 2007 at 7:11 PM
Message: In SQL Server 2005 have a Notification Services folder visible in SQL Mgmt Studio. When I run the query:

use master
alter database bersinektron SET ENABLE_BROKER

It says, "Executing Query" but never finishes the query. Just keeps running. Ive let the query run for over an hour, but still it never finishes. Is there a known error when running this query and how would I fix it to get the query to run so that I can Enable_Broker? thanks.


Subject: not working: Set Options
Posted by: Brad (view profile)
Posted on: Wednesday, September 12, 2007 at 10:41 AM
Message: I cannot get the SqlCacheDependency to work. When I run a trace, the QN:SUCSCRIPTION has a NotificationMsg with type="subscribe" source="statement" info="set options". I think that all of my SET options are correct, as I set them according to how MSDN says they should be set (http://msdn2.microsoft.com/en-us/library/aewzkxxh(vs.80).aspx). Could you look at this query and tell me if you see anything wrong?

USE [cacheTestDb]
GO

SET ANSI_NULLS ON
GO
SET ANSI_PADDING ON
GO
SET ANSI_WARNINGS ON
GO
SET CONCAT_NULL_YIELDS_NULL ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET NUMERIC_ROUNDABORT OFF
GO
SET ARITHABORT ON
GO

ALTER PROCEDURE [dbo].[usp_customers_by_site_id]
@site_id INT
AS

SELECT
customers.customer_id,
customers.name,
customers.po_prefix,
customers.dt_created,
customers.created_by AS auid,
customers.po_required
FROM dbo.customers
WHERE customers.site_id = @site_id
AND customers.is_active = 1



Subject: SqlCacheDependency
Posted by: Swapnil (not signed in)
Posted on: Tuesday, October 23, 2007 at 4:09 AM
Message: My SqlCacheDependency is working fine but i have to pust refresh button to see the updation.I want something else which will automatically refreshes the grid and lable.
Here is my code

protected void Page_Load(object sender, EventArgs e)
{

Label1.Text = "Cache Refresh: " + DateTime.Now.ToLongTimeString();
using (SqlConnection connection = new SqlConnection(GetConnectionString()))
{
using (SqlCommand command = new SqlCommand(GetSQL(), connection))
{
SqlCacheDependency dependency = new SqlCacheDependency(command);
int numberOfMinutes = 1;
DateTime expires = DateTime.Now.AddMinutes(numberOfMinutes);
Response.Cache.SetExpires(expires);
Response.Cache.SetCacheability(HttpCacheability.Public);
Response.Cache.SetValidUntilExpires(true);
Response.AddCacheDependency(dependency);
connection.Open();
GridView1.DataSource = command.ExecuteReader();
GridView1.DataBind();
}
}
}

private string GetConnectionString()
{
return ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString.ToString(); ;
}

private string GetSQL()
{
return "SELECT ID,Name from dbo.tbl_P";
}

Please tell me the exact process now.i am getting frustate to searching that kind of problem

Subject: SqlCacheDependency
Posted by: swapnil.malviya (view profile)
Posted on: Tuesday, October 23, 2007 at 4:12 AM
Message: My SqlCacheDependency is working fine but i have to pust refresh button to see the updation.I want something else which will automatically refreshes the grid and lable.
Here is my code

protected void Page_Load(object sender, EventArgs e)
{

Label1.Text = "Cache Refresh: " + DateTime.Now.ToLongTimeString();
using (SqlConnection connection = new SqlConnection(GetConnectionString()))
{
using (SqlCommand command = new SqlCommand(GetSQL(), connection))
{
SqlCacheDependency dependency = new SqlCacheDependency(command);
int numberOfMinutes = 1;
DateTime expires = DateTime.Now.AddMinutes(numberOfMinutes);
Response.Cache.SetExpires(expires);
Response.Cache.SetCacheability(HttpCacheability.Public);
Response.Cache.SetValidUntilExpires(true);
Response.AddCacheDependency(dependency);
connection.Open();
GridView1.DataSource = command.ExecuteReader();
GridView1.DataBind();
}
}
}

private string GetConnectionString()
{
return ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString.ToString(); ;
}

private string GetSQL()
{
return "SELECT ID,Name from dbo.tbl_P";
}

Please tell me the exact process now.i am getting frustate to searching that kind of problem

Subject: Monitoring the database
Posted by: Anonymous (not signed in)
Posted on: Tuesday, November 06, 2007 at 3:22 AM
Message: Please can any one help me how to monitor a database in sql server
like who use it etc

Subject: Useful help on Source = system and Info = invalid
Posted by: Kailas Tare (not signed in)
Posted on: Friday, January 18, 2008 at 2:23 PM
Message: I was getting "Source = system and Info = invalid" QN event. Your article helped me figure out that i means my SQL query had something wrong. I corrected the query and QN started working. Thanks a lot.

Subject: Query Notification works with SQLExpress!!
Posted by: Carlo (not signed in)
Posted on: Thursday, February 21, 2008 at 3:34 AM
Message: I use in my application QN with express version of sql2005!!!

Subject: I love this feature!
Posted by: Bob (view profile)
Posted on: Tuesday, April 01, 2008 at 1:35 PM
Message: Using .NET 2.0 with SQL Server 2005 (push model) has such a great advantage over using SQL Server 2000 (pull model). You really see the performance improvments with the push model since the database is not being polled every N seconds as in the pull model.

Bob
http://www.lessonplansforfree.com

Subject: its very good...
Posted by: chetan (not signed in)
Posted on: Monday, May 05, 2008 at 8:51 AM
Message: i had a proble that notification not working but after i read this artical... i have fixed that problem. really this artical has helped me so much... thank you very very much...

Subject: Please can you help
Posted by: Karen (not signed in)
Posted on: Thursday, May 15, 2008 at 11:12 AM
Message: Hi Great article but I am still having an issue, please can anyone help?

Here is the event from the sql profiler as you can see the subscription was not registered properly. From your article you suggest that if the Source = system and Info = invalid. That is indicate that an Update or Insert statement is being used. I am using a straight SELECT, any other ideas what may be causing this issue?

<qnev:QNEvent xmlns:qnev="http://schemas.microsoft.com/SQL/Notifications/QueryNotificationProfiler"><qnev:EventText>subscription
fired</qnev:EventText><qnev:SubscriptionID>0</qnev:SubscriptionID><qnev:NotificationMsg><qn:QueryNotification xmlns:qn="http://schemas.microsoft.com/SQL/Notifications/QueryNotification" id="0"
type="subscribe" source="statement" info="invalid" database_id="0"
sid="0x3425643468FF7F4F8E3CF3C8B14A2869"><qn:Message>e48e9cbb-3cf0-4a3c-ab01-6e0e09d87006;e66804b7ee3a1096c4a2a124f575c385383148cc</qn:Message></qn:QueryNotification></qnev:NotificationMsg><qnev:BrokerDlg>DCDD186A-9622-DD11-8B11-000A5E1FF2FD</qnev:BrokerDlg></qnev:QNEvent>


Here is a copy of my code

string connectionString =
System.Configuration.ConfigurationManager.ConnectionStrings["LocalSqlServer"].ConnectionString;

DataTable Customer = new DataTable();

using (SqlConnection connection =
new SqlConnection(connectionString))
{
SqlDependency.Start(connectionString);
SqlCommand command = new SqlCommand(
"SELECT CustName, TimeZone, CustData, TMSData from dbo.tCustomer", connection);

System.Web.Caching.SqlCacheDependency new_dependency =
new System.Web.Caching.SqlCacheDependency(command);

SqlDataAdapter DA1 = new SqlDataAdapter();
DA1.SelectCommand = command;

DataSet DS1 = new DataSet();

DA1.Fill(DS1);

Customer = DS1.Tables[0];

HttpContext.Current.Cache.Insert("Customer", Customer, new_dependency);
//HttpContext.Current.Cache.Insert("Customer", Customer);
}

return Customer;
}

Subject: Query Notification not triggering always
Posted by: Anonymous (not signed in)
Posted on: Thursday, May 29, 2008 at 11:41 AM
Message: I have set up my code to use SQL cache dependency on a table at a row level. Once i update a row in the table, i see the notification being fired (from the SQL profiler), But when i do another update (with a change in the row data), i dont see the notifictain being fired and my cache is not getting invalidated.

Any idea why this is showing inconsistent behaviour ?

Appreciate respone..
Thanks

Subject: sql2005 express engine startup notification.
Posted by: grumpy (view profile)
Posted on: Saturday, February 07, 2009 at 9:54 AM
Message: I have a automation server that starts on machine startup. It needs to make contact with the sql server, but just repeatedly doing a simple "are you there" query seems too messy. Is there a sure way to find out if the db is up and running( without any .NET stuff and in C++ please )

Subject: WinForm + WCF + stored procedure
Posted by: sheir (view profile)
Posted on: Tuesday, April 07, 2009 at 6:04 PM
Message: Hi,
I have a winform application that talks to a WCF service (just to investigate QN) and I use a simple SELECT stored procedure on the Northwind database.

The app works fine if I DO NOT use a stored proc but if I use the stored proc (same exact SQL SELECT statement); I do not get any notifications.

Why would it work with the actual SELECT statement but not if that same select statement is in a stored procedure?

Any ideas?
Thanks

Subject: Subscription Fired, but cache is NOT invalidated.
Posted by: wxp (view profile)
Posted on: Thursday, May 28, 2009 at 10:57 AM
Message: I follow exactly the steps of this article,
and it works on my home computer. But it does NOT work for my office computer, they are same codes. why?

From SQL Server Profiler, I can see the subscription fired successfully after I update some data. But cache is still NOT invalidated.
See trace info below. Please help me!!!!

<qnev:QNEvent xmlns:qnev="http://schemas.microsoft.com/SQL/Notifications/QueryNotificationProfiler"><qnev:EventText>subscription registered</qnev:EventText><qnev:SubscriptionID>13</qnev:SubscriptionID></qnev:QNEvent>

<qnev:QNEvent xmlns:qnev="http://schemas.microsoft.com/SQL/Notifications/QueryNotificationProfiler"><qnev:EventText>subscription fired</qnev:EventText><qnev:SubscriptionID>13</qnev:SubscriptionID><qnev:NotificationMsg><qn:QueryNotification xmlns:qn="http://schemas.microsoft.com/SQL/Notifications/QueryNotification" id="13" type="change" source="data" info="update" database_id="7" sid="0x8ED26902D62E2C42B90BA62CBA081A35"><qn:Message>9c43124b-616c-444d-b92d-5f0d20daf412;4ba4c8be-f9e7-4dfe-ab92-9dc0fc54139f</qn:Message></qn:QueryNotification></qnev:NotificationMsg><qnev:BrokerDlg>DA76BAFE-FE4A-DE11-B965-005056C00008</qnev:BrokerDlg></qnev:QNEvent>

Subject: oh, here is my code
Posted by: wxp (view profile)
Posted on: Friday, May 29, 2009 at 12:36 PM
Message: private static void CacheData_WithSQLCacheDependency()
{
// Fetch all accounts' info and insert into cache
//
SqlConnection Conn;
Conn = new SqlConnection(connStr);

try
{
Conn.Open();

string strQuery = "";
strQuery += " SELECT CardNo, PinNumber, Fines_Owing, CardExpiry ";
strQuery += " FROM dbo.Patron2 ";
SqlCommand cmd = new SqlCommand(strQuery, Conn);
SqlCacheDependency sqlDependency = new SqlCacheDependency(cmd);

SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
DataSet ds = new DataSet();
da.Fill(ds, "dtAllPatrons");
DataTable dtAccountInfos = new DataTable();
dtAccountInfos = ds.Tables["dtAllPatrons"];

HttpRuntime.Cache.Insert("AccountInfos",
dtAccountInfos,
sqlDependency,
Cache.NoAbsoluteExpiration,
Cache.NoSlidingExpiration);

DataTable dtTemp = (DataTable)HttpRuntime.Cache["AccountInfos"];

}
}
catch (WebException webEx)
{

}
catch (Exception ex)
{

}
finally
{
Conn.Close();
}


}

Subject: here is the answer
Posted by: wxp (view profile)
Posted on: Monday, June 01, 2009 at 10:57 AM
Message: Wow, I found the answer myself. pleased to share:
Query Notifications run 'execute as owner' so if a database is owned by a domain account, query notifications fail in the event a domain control cannot be contacted

Basically, change owner to SA to avoid the issue.


Subject: Answer
Posted by: wxp (view profile)
Posted on: Monday, June 01, 2009 at 10:58 AM
Message: Query Notifications run 'execute as owner' so if a database is owned by a domain account, query notifications fail in the event a domain control cannot be contacted



Basically, change owner to SA to avoid the issue.

See this writeup

http://aspadvice.com/blogs/ssmith/archive/2006/11/06/SqlDependency-Issue-Resolved.aspx


Subject: When sql server restarted
Posted by: Rajesh_vellore (view profile)
Posted on: Thursday, July 02, 2009 at 11:01 AM
Message: When sql server got restarted sqldependency connectivity between db server and client code are being disconnected..

so when the sevice is up when there is change in the sql dependency query on change event is not fired..


Anyone can provide solution for this..Plz

Subject: When sql server restarted
Posted by: Rajesh_vellore (view profile)
Posted on: Thursday, July 02, 2009 at 11:33 AM
Message: When sql server got restarted sqldependency connectivity between db server and client code are being disconnected..

so when the sevice is up when there is change in the sql dependency query on change event is not fired..


Anyone can provide solution for this..Plz

Subject: When sql server restarted
Posted by: Rajesh_vellore (view profile)
Posted on: Thursday, July 02, 2009 at 12:20 PM
Message: When sql server got restarted sqldependency connectivity between db server and client code are being disconnected..

so when the sevice is up when there is change in the sql dependency query on change event is not fired..


Anyone can provide solution for this..Plz

Subject: When sql server restarted
Posted by: Rajesh_vellore (view profile)
Posted on: Thursday, July 02, 2009 at 12:21 PM
Message: When sql server got restarted sqldependency connectivity between db server and client code are being disconnected..

so when the sevice is up when there is change in the sql dependency query on change event is not fired..


Anyone can provide solution for this..Plz

Subject: No Notifications
Posted by: billr578 (view profile)
Posted on: Wednesday, March 10, 2010 at 2:46 PM
Message: Hi,

Great article!

I was wondering if you had any tips if I'm not seeing any query notification messages in SQL Profiler?

I've set my app up following all of the steps above, but after inserting my data into the cache with a SqlCacheDependency, the cached data is never invalidated and removed so it can be fetched with the new values from the database.

Thanks!

-Bill

 

Phil Factor
Searching for Strings in SQL Server Databases

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

 View the blog

Top Rated

Searching for Strings in SQL Server Databases
 Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine... Read more...

The SQL Server Sqlio Utility
 If, before deployment, you need to push the limits of your disk subsystem in order to determine whether... Read more...

The PoSh DBA - Reading and Filtering Errors
 DBAs regularly need to keep an eye on the error logs of all their SQL Servers, and the event logs of... Read more...

MySQL Compare: The Manual That Time Forgot, Part 1
 Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... Read more...

Highway to Database Recovery
 Discover the best backup and recovery articles on Simple-Talk, all in one place. Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.