Wicked walkthough.

Hi,
I am new in SQL DBA, i want to create DTS in SQL Server 2005.
Can you help me.
Thnx

For those of you using SQL 2005 Express Edition, the Query Notifications feature is not available.

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

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

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>

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

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.

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?

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

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

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.

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.