SQL Cache Dependency not working with Stored Procedure

Question

Hello,

I can't get SqlCacheDependency to work with a simple stored proc (SQL Server 2008):

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

And here's my ASP.NET code (3.5 framework):

-- 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;

}

Anyone have any luck with getting this to work with SPs? Thanks!

Answer 1

i figured this out, need to set query options BEFORE creating the SP. got it working when i created the SP as follows:

USE [MyDatabase]
GO

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
go


create proc [dbo].[spGetPeteTest]
as

select Id, Artist, Album
from dbo.PeteTest

GO

Answer 2

hi i use ur example to apply sql SQL Server Cache Dependency with stored procedure but i fount that the web cashing alwayes null which means that if there is not changes in data base my Grid View not read from webcashe but connect on data base please i want to know is it happen with you?? best wishes fatma

Answer 3

Hi,

I am getting the same problem as fatma. I have used your code above and set up the stored procedure as you described. My stored procedure is a very simple select statement like:

"select id, name from dbo.tblTable;"

I have also setup the db with the sql scripts below that I have found from various posts.

However my cache is always invalidated immediately. This still happens even when I put the select statement directly into the sqlcommand and don't use a stored procedure!

Can anyone help? I've been stuck on this for quiet some time!

Thanks

Paul

------------------------------------------SQL Script Start------------------------------

use db_Company; ALTER DATABASE db_Company SET ENABLE_BROKER; select is_broker_enabled from sys.databases where name = 'db_Company'

EXEC sp_addrole 'sql_dependency_subscriber'

GRANT CREATE PROCEDURE TO db_user; GRANT CREATE QUEUE TO db_user; GRANT CREATE SERVICE TO db_user; GRANT REFERENCES on CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] to db_user GRANT VIEW DEFINITION TO db_user

GRANT SUBSCRIBE QUERY NOTIFICATIONS TO db_user;

use db_Company; GRANT SELECT ON OBJECT::dbo.tblCompanyEnumValue TO db_user;

Use db_Company; GRANT RECEIVE ON QueryNotificationErrorsQueue TO db_user;

GRANT SELECT to db_user GRANT RECEIVE ON QueryNotificationErrorsQueue TO db_user GRANT REFERENCES on CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] to db_user EXEC sp_addrolemember 'sql_dependency_subscriber', 'db_user'

exec sp_helprotect NULL, 'db_user'

GRANT CONTROL ON SCHEMA::[dbo] TO [db_user]; GRANT IMPERSONATE ON USER::DBO TO [db_user];

------------------------------------------SQL Script end------------------------------

Answer 4

For me using something like this in the stored proc didn't work:

"select id, name from dbo.tblTable;"

I had to explicitly put in the references like this:

"select dbo.tblTable.id, dbo.tblTable.name from dbo.tblTable;"

Then it worked for me.