Can't get SqlCacheDependency working correctly

Discussion in 'ASP .Net' started by Joel Barsotti, Feb 7, 2006.

  1. I'm trying to get sql cache dependency working correctly.

    I've got sql server 2005 for my back end.

    I've don the "ALTER DATABASE dbName SET broker_enable" command.

    I made sure the user in my connection string has subscripe notifications
    permission set to grant.

    But now it seems that the cache is kicking my product out of the cache

    I'm using Cache.Insert(prodName, productObject, prodDepenancy) to put
    stuff into the cache, and when I go to refresh the page Cache[prodName] has
    reverted to null.

    Any ideas?
    Joel Barsotti, Feb 7, 2006
    1. Advertisements

  2. Hi Joel,

    Currently we are looking for some people to help you on this issue, we will
    update you ASAP. Thanks for your understanding

    Best regards,
    Jeffrey Tan
    Microsoft Online Partner Support
    Get Secure! -
    This posting is provided "as is" with no warranties and confers no rights.
    Jeffrey Tan[MSFT], Feb 8, 2006
    1. Advertisements

  3. Hi Joel,

    Welcome to the MSDN newsgroup.

    As for the SQL Cache dependency in ASP.NET 2.0, we need to make sure the
    configuration in the following places are correct:

    1. In sqlserver, we need to make sure we've enabled the sql cache through
    aspnet_regsql.exe tool

    2. In ASP.NET web application, ensure that web.config file contains the
    correct setting for that certain sql cache database/table

    If the above are all correctly configured, there may have something else
    cause the problem. And for general throubleshooting, you can try the
    following means also:

    a.Use Sql Profiler to monitor the SQL Server database server to see whether
    there're sql query operations peformed by sql cache thread...

    b.Add a RemoveCallBack handler for the cached item to see whether it is
    called immediately after you add the cache item.

    In addition, here is the walkthrough article in MSDN on using sql cache
    dependency in 2.0, you can also try testing through this to see
    whether it can work:

    #Walkthrough: Using ASP.NET Output Caching with SQL Server

    Hope this helps.


    Steven Cheng
    Microsoft Online Support

    Get Secure!
    (This posting is provided "AS IS", with no warranties, and confers no
    Steven Cheng[MSFT], Feb 9, 2006
  4. Joel Barsotti

    chris Guest


    It was my understanding that you step 1 and 2 were only needed if you
    were hooking to a Sql 2000 server and Sql 2005. In order to get mine
    working all I needed to do was have a user with the right permissions
    () and then make sure he was hooked to a custom schema and not the dbo
    schema. Also, I am sure you probably have this, otherwise it would be
    giving you an error, but make sure to call SqlDependency.Start.

    void Application_Start(object sender, EventArgs e)


    Here is my code that works for me. Note that I have my application
    abstracted to multiple layers.
    Data Layer:
    public DataReader GetLists(Guid ownerid, ref SqlCacheDependency
    //Note that _Command and _Connection are already declared as
    new SqlCommand and SqlConnection objects respectively by this point.
    _Command.Parameters.Add(new SqlParameter("@OwnerID",
    SqlDbType.UniqueIdentifier, 0));
    _Command.Parameters["@OwnerID"].Value = ownerid;

    spname = "[dbo].[usp_ListGetList]";
    _Command.Connection = _Connection;
    _Command.CommandType = CommandType.StoredProcedure;
    _Command.CommandText = spname;

    scd = new SqlCacheDependency(_Command);

    return new DataReader(_Command.ExecuteReader());


    Here is my Business Layer
    public Dictionary<string, string> LoadDictionary()
    Dictionary<string, string> dict = new Dictionary<string,

    Cache cache = HttpRuntime.Cache;
    string cacheKey = "List::" + _OwnerID.ToString();
    if (cache[cacheKey] == null)
    dict.Add("", "Choose One...");

    //Note that SqlBuilder is a custom wrapper class that
    contains all db calls like the one shown above
    using (SqlBuilder builder = new SqlBuilder())
    SqlCacheDependency scd = null;
    using (DataReader reader =
    builder.GetLists(_OwnerID, ref scd))
    while (reader.Read())

    //insert this into cache with a sql cache
    cache.Insert(cacheKey, dict, scd);

    dict = cache[cacheKey] as Dictionary<string, string>;

    return dict;

    This took me a little while to get working, but I was having
    permissions issues and not the issue you were having. But anyway I
    hope this help because once you get this working it is very slick.

    chris, Feb 9, 2006
  5. Any ideas yet?
    Joel Barsotti, Feb 14, 2006
  6. Hi Joel,

    Have you also viewed Chris and mine response in this thread?


    Steven Cheng
    Microsoft Online Support

    Get Secure!
    (This posting is provided "AS IS", with no warranties, and confers no
    Steven Cheng[MSFT], Feb 15, 2006
  7. Joel Barsotti


    Jun 5, 2009
    Likes Received:
    Can't get SqlCacheDependency working correctly Reply to Thread

    Hi Can anyone please help me .i m getting data in dataset from store procedure and stored it in cache its work fine but dataset not refreshed when storeprocedure result changes.
    poojamca, Nov 24, 2009
  8. Joel Barsotti


    Dec 21, 2009
    Likes Received:
    SQL Cache dependency not working properly

    HI Experts i am using sql cache dependency in my application and it is working fine on my local machine but when i port it to the production server the application performs caching for few initial combinations but after that the application slows down tremendiously.
    On running cleartemp on the server and on changing the dependent tables the application starts running again but the issue crops up again after sometime. Please note that the database and application are on 2 seperate servers and cleartemp is hit on application server. Can anyone please help!!!
    dhaval42, Dec 21, 2009
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.