Issue with SQLCacheDepende ncy

Hey Guys,

I have been struggling with this for few days. when i use

SqlCacheDepende ncy sqlDependency=n ew
SqlCacheDepende ncy("test","dbo .USStates");

where test is the name of connectionstrin g and dbo.USStates is the table
name everything works fine.

but when i use
using (SqlConnection conn=new SqlConnection(c onnectionString ))

{

SqlCommand command = new SqlCommand("[dbo].[USStatesGet]", conn);

command.Command Type = CommandType.Sto redProcedure;

SqlCacheDepende ncy sqlDependency = new SqlCacheDepende ncy(command);

}

HttpRuntime.Cac he.Insert(cache Name, ds, sqlDependency,
Cache.NoAbsolut eExpiration,Cac he.NoSlidingExp iration);

then the cache is not being invalidated when the values are changed in the
table

the following is the stored procedure i am using
set ANSI_NULLS ON

set QUOTED_IDENTIFI ER ON

GO

ALTER PROCEDURE [dbo].[USStatesGet]

-- Add the parameters for the stored procedure here

AS

BEGIN

--SET NOCOUNT OFF;

SELECT [stateid]

,[statename]

FROM [dbo].[USStates]

END

Thanks in Advance

Param

[bruce barker]

assuming your using sql2005, sqlcachedepende ncy uses sql2005 query
notification (otherwise, it just polls using the query). query
notification has several restrictions. see:

http://msdn2.microsoft.com/en-US/library/ms181122.aspx

-- bruce (sqlwork.com)

pa***@community .nospam wrote:

Hey Guys,

I have been struggling with this for few days. when i use

SqlCacheDepende ncy sqlDependency=n ew
SqlCacheDepende ncy("test","dbo .USStates");

where test is the name of connectionstrin g and dbo.USStates is the table
name everything works fine.

but when i use
using (SqlConnection conn=new SqlConnection(c onnectionString ))

{

SqlCommand command = new SqlCommand("[dbo].[USStatesGet]", conn);

command.Command Type = CommandType.Sto redProcedure;

SqlCacheDepende ncy sqlDependency = new SqlCacheDepende ncy(command);

}

HttpRuntime.Cac he.Insert(cache Name, ds, sqlDependency,
Cache.NoAbsolut eExpiration,Cac he.NoSlidingExp iration);

then the cache is not being invalidated when the values are changed in the
table

the following is the stored procedure i am using
set ANSI_NULLS ON

set QUOTED_IDENTIFI ER ON

GO

ALTER PROCEDURE [dbo].[USStatesGet]

-- Add the parameters for the stored procedure here

AS

BEGIN

--SET NOCOUNT OFF;

SELECT [stateid]

,[statename]

FROM [dbo].[USStates]

END

Thanks in Advance

Param