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

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

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

Please can any one help me how to monitor a database in sql server
like who use it etc

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.

I use in my application QN with express version of sql2005!!!

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

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

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;
}

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

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 )

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

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>

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();
}


}

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.