title: How to use SqlCacheDependency?
body: <p>I need to implement SqlCacheDependency for a table which will depend on this query:
  <code>SELECT Nickname FROM dbo.[User]</code>.</p>
  
  <p>I have created a method for this purpose:</p>
  
  <pre><code>private IEnumerable&lt;string&gt; GetNicknamesFromCache()
      {
          const String cacheValueName = "Nicknames";
  
          var result = HttpRuntime.Cache.Get(cacheValueName) as List&lt;String&gt;;
          if (result == null)
          {
              result = _repository.GetAllNicknames();
  
              var connectionString = ConfigurationManager.ConnectionStrings["RepositoryContext"].ConnectionString;
              var sqlConnection = new SqlConnection(connectionString);
              var sqlCommand = new SqlCommand("SELECT Nickname FROM dbo.[User]", sqlConnection);
              var sqlDependency = new SqlCacheDependency(sqlCommand);
  
              HttpRuntime.Cache.Insert(cacheValueName, result, sqlDependency);
          }
  
          return result;
      }
  </code></pre>
  
  <p>But when I run my application it doesn't work.
  I checked the list of subscribers (<code>sys.dm_qn_subscriptions</code> table) and there was no records.</p>
  
  <p>I investigated much time and have already tried various solutions but they doesn't work for me:</p>
  
  <ul>
  <li><p>use trusted connection and set some permissions for public role:</p>
  
  <p><code>GRANT CREATE PROCEDURE TO public</code><br>
  <code>GRANT CREATE QUEUE TO public</code><br>
  <code>GRANT CREATE SERVICE TO public</code><br>
  <code>GRANT SUBSCRIBE QUERY NOTIFICATIONS TO public</code><br>
  <code>GRANT SELECT ON OBJECT::dbo.[User] TO public</code><br>
  <code>GRANT RECEIVE ON QueryNotificationErrorsQueue TO public</code><br></p></li>
  <li><p>use 'sa' login for connection</p></li>
  <li>use aspnet_regsql.exe (<code>aspnet_regsql.exe -S localhost -E -ed -d TestTable -et -t User</code>)</li>
  <li><p>add configuration to system.webServer in web.config:</p>
  
  <p><code>&lt;caching&gt;</code><br>
    <code>&lt;sqlCacheDependency enabled="true"&gt;</code><br>
      <code>&lt;databases&gt;</code><br>
        <code>&lt;add name="Tmpl" pollTime="5000" connectionStringName="RepositoryContext"/&gt;</code><br>
      <code>&lt;/databases&gt;</code><br>
    <code>&lt;/sqlCacheDependency&gt;</code><br>
  <code>&lt;/caching&gt;</code><br></p></li>
  <li><p>put the SqlDependency.Start() into the Global.asax Application_Start event</p></li>
  <li><p>run at different instances of sql server (SQL Server 2008 Express, SQL Server 2008)</p></li>
  </ul>
  
  <p>But It didn't help. It still doesn't work.</p>
  
  <p>How do I make it work?</p>