locked
SqlCacheDependency Error - Database Not Enabled for SQL cache notification RRS feed

  • Question

  • User-939035612 posted

    I followed this example exactly https://docs.microsoft.com/en-us/dotnet/api/system.web.ui.webcontrols.sqldatasource.sqlcachedependency?view=netframework-4.8 but when I run the site I get this. My source code is like this:

    <caching>
          <sqlCacheDependency enabled="true">
            <databases>
              <add name="MyDB" connectionStringName="MyConnectionString" pollTime="600"/>
            </databases>
          </sqlCacheDependency>
        </caching>

    On my page:

    <asp:SqlDataSource ID="reportsSqlDataSource" runat="server" EnableCaching="True" CacheDuration="60" CacheExpirationPolicy="Sliding" CacheKeyDependency="HomePosts" DataSourceMode="DataSet" SqlCacheDependency="MyDB:posts" CancelSelectOnNullParameter="False" ConnectionString="<%$ ConnectionStrings:cblasterConnectionString %>" SelectCommand="SELECT posts.postid, posts.posttitle, posts.city, posts.image, posts.hashtag, posts.imagename, countries.countryname, countries.countryslug, states.statename, states.stateslug, categories.catname, categories.catslug, subcategories.scatname, subcategories.scatslug FROM posts INNER JOIN categories ON posts.category = categories.catid INNER JOIN countries ON posts.country = countries.countryid INNER JOIN locationstatus ON posts.locationstatus = locationstatus.lsid INNER JOIN locationtypes ON posts.locationtype = locationtypes.ltid INNER JOIN states ON posts.state = states.stateid INNER JOIN subcategories ON posts.subcategory = subcategories.scatid WHERE (posts.banned = 0) AND (posts.active = 1) AND (posts.posttitle LIKE '%' + @search + '%' OR posts.postbody LIKE '%' + @search + '%' OR posts.city LIKE '%' + @search + '%' OR posts.hashtag LIKE '%' + @search + '%' OR @search IS NULL) AND (posts.category = @category OR @category IS NULL) AND (posts.subcategory = @subcategory OR @subcategory IS NULL) AND (posts.state = @state OR @state = 0) ORDER BY posts.postid DESC">

    According to the Microsoft example nothing more is required, but when I run the site I get this:

    Server Error in '/' Application. The database 'MyDB' is not enabled for SQL cache notification. To enable a database for SQL cache notification, please use the System.Web.Caching.SqlCacheDependencyAdmin.EnableNotifications method, or the command line tool aspnet_regsql. To use the tool, please run 'aspnet_regsql.exe -?' for more information. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Web.Caching.DatabaseNotEnabledForNotificationException: The database 'MyDB' is not enabled for SQL cache notification. To enable a database for SQL cache notification, please use the System.Web.Caching.SqlCacheDependencyAdmin.EnableNotifications method, or the command line tool aspnet_regsql. To use the tool, please run 'aspnet_regsql.exe -?' for more information. Source Error: The source code that generated this unhandled exception can only be shown when compiled in debug mode. To enable this, please follow one of the below steps, then request the URL: 1. Add a "Debug=true" directive at the top of the file that generated the error. Example: <%@ Page Language="C#" Debug="true" %> or: 2) Add the following section to the configuration file of your application: Note that this second technique will cause all files within a given application to be compiled in debug mode. The first technique will cause only that particular file to be compiled in debug mode. Important: Running applications in debug mode does incur a memory/performance overhead. You should make sure that an application has debugging disabled before deploying into production scenario. Stack Trace: [DatabaseNotEnabledForNotificationException: The database 'MyDB' is not enabled for SQL cache notification. To enable a database for SQL cache notification, please use the System.Web.Caching.SqlCacheDependencyAdmin.EnableNotifications method, or the command line tool aspnet_regsql. To use the tool, please run 'aspnet_regsql.exe -?' for more information.] System.Web.Caching.SqlCacheDependencyManager.EnsureTableIsRegisteredAndPolled(String database, String table) +2971525 System.Web.Caching.SqlCacheDependency.GetDependKey(String database, String tableName) +63 System.Web.Caching.SqlCacheDependency..ctor(String databaseEntryName, String tableName) +39 System.Web.Caching.SqlCacheDependency.CreateSql7SqlCacheDependencyForOutputCache(String database, String table, String depString) +50 System.Web.Caching.SqlCacheDependency.CreateOutputCacheDependency(String dependency) +236 System.Web.UI.SqlDataSourceCache.SaveDataToCacheInternal(String key, Object data, CacheDependency dependency) +63 System.Web.UI.WebControls.SqlDataSource.SaveDataToCache(Int32 startRowIndex, Int32 maximumRows, Object data, CacheDependency dependency) +121 System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1744 _Default.Page_Load(Object sender, EventArgs e) +1354 System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +52 System.Web.UI.Control.OnLoad(EventArgs e) +97 System.Web.UI.d__246.MoveNext() +229 System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +102 System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +64 System.Web.Util.WithinCancellableCallbackTaskAwaiter.GetResult() +32 System.Web.UI.d__523.MoveNext() +4853

    After enabling debugging, the error was traced to a line of code behind. That line is as follows:

    Line 69:         DataView reports = (DataView)reportsSqlDataSource.Select(DataSourceSelectArguments.Empty);
    Monday, September 28, 2020 5:17 AM

All replies

  • User-939035612 posted

    I tried using the command line to register the database as instructed here https://www.codeproject.com/Articles/14976/ASP-NET-Caching-Dependencies, but got the error "Unable to connect to the SQL database for cache dependency registration." That makes no sense because it is the same connection string that the application always is able to connect to.

    Monday, September 28, 2020 5:33 AM
  • User-939035612 posted

    Double checking the command line I used. It was the correct username and password for the database but still would not connect

    Monday, September 28, 2020 6:54 AM
  • User-939035612 posted

    I really need a way to enable the SQLCacheDependency table without relying on either csharp code or the .exe utility. I say this because the remote DB that connects to the live version of the app cannot be accessed remotely from web.config files no matter what I do. I try using the IP/SQLEXRESS for the instance name like I do every other DB on that server but it never connects, but I can't find any examples for enabling SQL cache notification using SSMS. I can login to via SSMS but never from a web application from my computer. I have never had this problem with any DB but this one before or since and I can't figure out what is different about this one.

    Monday, September 28, 2020 7:29 AM
  • User-939035612 posted

    This works for a different database with a different project on the SAME server:

    aspnet_regsql -ed -S ipaddress\SQLEXPRESS -d dbname -U username -P password

    Monday, September 28, 2020 8:24 AM
  • User-939850651 posted

    Hi CopBlaster,

    I used the relevant settings you provided to conduct a similar test. And it has already given an accurate solution in the response error message.

    You could try to add the following code in the appropriate place:

    string conStr = ConfigurationManager.ConnectionStrings["MyConnectionString"].ToString();
    SqlCacheDependencyAdmin.EnableNotifications(conStr); Or
    string conStr = ConfigurationManager.ConnectionStrings["MyConnectionString"].ToString(); SqlCacheDependencyAdmin.EnableTableForNotifications(conStr, "posts");

    And It works normally after this.

    For more details, please refer to SqlCacheDependency Constructors.

    Hope this can help you.

    Best regards,

    Xudong Peng

    Tuesday, September 29, 2020 3:17 AM
  • User-939035612 posted

    Thanks. I actually ended up using the utility to enable each table and then the configuration in the Web.Config worked

    Wednesday, September 30, 2020 9:43 AM