Issue with SQLCacheDependency

[Guest]

Hey Guys,



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

SqlCacheDependency sqlDependency=new
SqlCacheDependency("test","dbo.USStates");

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

but when i use


using (SqlConnection conn=new SqlConnection(connectionString))

{

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

command.CommandType = CommandType.StoredProcedure;

SqlCacheDependency sqlDependency = new SqlCacheDependency(command);

}

HttpRuntime.Cache.Insert(cacheName, ds, sqlDependency,
Cache.NoAbsoluteExpiration,Cache.NoSlidingExpiration);



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_IDENTIFIER 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, sqlcachedependency 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)