SqlCacheDependency not working

I would like to add SqlCacheDependency to my app. So I desided to create littel tesp project and confronted with difficulties. I use MSSQL 2008. I create new db with table Lines and added several rows. I executed:

ALTER DATABASE ForTest SET ENABLE_BROKER 

in managmeng studio.

Aspx page:

public partial class WebForm1 : System.Web.UI.Page
  {
    protected void Page_Load(object sender, EventArgs e)
    {
      if (!IsPostBack)
      {
       if (Cache["Lines"] == null)
       {
         string connectionString = 
           WebConfigurationManager.ConnectionStrings["ForTest"].ConnectionString;
         SqlConnection con = new SqlConnection(connectionString);
         string query = "SELECT dbo.Lines.Id, dbo.Lines.Value FROM dbo.Lines";
         SqlCommand cmd = new SqlCommand(query, con);
         SqlDataAdapter adapter = new SqlDataAdapter(cmd);

         DataSet ds = new DataSet();
         adapter.Fill(ds, "Lines"); 

         SqlCacheDependency empDependency = new SqlCacheDependency(cmd);
         Cache.Insert("Lines", ds, empDependency); 
       }
      }
    }

    protected void btnResult_OnClick(object sender, EventArgs e)
  开发者_开发问答  {
      var result = Cache["Lines"];
    }
}

I run this page and add lines to Cache then I add row in managment studio and when I click on button I expect that the cache will be changed but cache remains old. I can't find what I do wrong :( Could you give me some hint how I can solve this problem?

Thanks

Update: I forger to say that in global.aspx I run:

SqlDependency.Start(
    WebConfigurationManager.ConnectionStrings["ForTest"].ConnectionString
);

---

I had a similar issue. This article: Troubleshooting SqlCacheDependency in SQL Server 2008 and SQL Server 2005 helped me a lot then.

In a few words: the databse was restored from a backup, and the original Windows user that created the database was no longer available. So I changed the database ownership to a valid login, something similar to:

ALTER AUTHORIZATION ON DATABASE::[my_perfect_database_name] TO [sa];

and it works like a charm now.

How did I find the source of the issue? I run the query SELECT * FROM sys.transmission_queue and found the next data in the transmission_status column:

An exception occurred while enqueueing a message in the target queue. Error: 15517, State: 1. Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.

This message gave me a key to solving the problem.

---

There is a mistake in the code;

Definition of sqldependency should be placed before you execute the command, otherwise it will not subscribe to your sqlcommand, then it won't get notified when your resultset of your command changes.

SqlCacheDependency empDependency = new SqlCacheDependency(cmd);

DataSet ds = new DataSet();
adapter.Fill(ds, "Lines"); 

---

I also thought I had an issue with the SqlCacheDependency not being cleared after a change to the table.

Turns out it was because of how I was testing. I was simply editing the rows in the SQL table thru management studio and expecting it to notify and clear the cache. That's not the case! If you are editing the table, you must also re-execute the select sql to kick off the clearing of the cache.