title: SQL Cache Dependency not working with Stored Procedure
body: <p>I can't get SqlCacheDependency to work with a simple stored proc (SQL Server 2008):</p>
  
  <pre><code>create proc dbo.spGetPeteTest
  as
  
  set  ANSI_NULLS ON
  set ANSI_PADDING ON
  set ANSI_WARNINGS ON
  set CONCAT_NULL_YIELDS_NULL ON
  set QUOTED_IDENTIFIER ON
  set NUMERIC_ROUNDABORT OFF
  set ARITHABORT ON
  
  select Id, Artist, Album
  from dbo.PeteTest
  </code></pre>
  
  <p>And here's my ASP.NET code (3.5 framework):</p>
  
  <pre><code>-- global.asax
      protected void Application_Start(object sender, EventArgs e)
  {
      string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["MyConn"].ConnectionString;
      System.Data.SqlClient.SqlDependency.Start(connectionString);
  }
  
   -- Code-Behind
  private DataTable GetAlbums()
  {
      string connectionString =
      System.Configuration.ConfigurationManager.ConnectionStrings["UnigoConnection"].ConnectionString;
  
      DataTable dtAlbums = new DataTable();
  
      using (SqlConnection connection =
          new SqlConnection(connectionString))
      {
      // Works using select statement, but NOT SP with same text
      //SqlCommand command = new SqlCommand(
      //    "select Id, Artist, Album from dbo.PeteTest", connection);
      SqlCommand command = new SqlCommand();
      command.Connection = connection;
      command.CommandType = CommandType.StoredProcedure;
      command.CommandText = "dbo.spGetPeteTest";
  
  
      System.Web.Caching.SqlCacheDependency new_dependency =
          new System.Web.Caching.SqlCacheDependency(command);
  
  
      SqlDataAdapter DA1 = new SqlDataAdapter();
      DA1.SelectCommand = command;
  
      DataSet DS1 = new DataSet();
  
      DA1.Fill(DS1);
  
      dtAlbums = DS1.Tables[0];
  
      Cache.Insert("Albums", dtAlbums, new_dependency);
      }
  
      return dtAlbums;
  
  }
  </code></pre>
  
  <p>Anyone have any luck with getting this to work with SPs?
  Thanks!</p>