Cache drop if query has WHERE Clause

Question

Hi, I am using SqlDependency and CacheManager class(Enterprise library Cachcing block) to create a cache in one of vb.net project.

I have registerd

OnDependencyChanged(ByVal sender As Object, ByVal e As SqlNotificationEventArgs)

method to get the notification back from Sql Server 2005.

Now the problem is, if the query does not contains any WHERE clause then cache gets created successfully but if I add a where clause in my query then OnDependencyChanged() method gets notification as soon as cache is created.

I need suggestion whether it is possible to have a WHERE clause in the query that is being used for caching. My query is (with WHERE Clause):

SELECT [DATABASES_ID]  
      ,[DATABASENAME]
      ,[SERVERNAME]
  FROM [dbo].[DATABASES]
  WHERE datediff(dd,getdate(), [DATECREATED]) <=0

Thanks Brijesh

Answer 1

Check out this article outlying the limitations of SQLDepenency queries

http://msdn.microsoft.com/en-us/library/aewzkxxh.aspx

Specifically of Note:

The statement must not use any nondeterministic functions, including ranking and windowing functions.

GETDATE() being one such function.