search

Alachisoft / NCache

NCache: Highly Scalable Distributed Cache for .NET
http://www.alachisoft.com
Apache License 2.0
647 stars 123 forks source link

Item Removed Immediately after Addition into Cache when I use SqlCacheDependency #37

Open nofalfayyaz opened 5 years ago

nofalfayyaz commented 5 years ago

We are trying to implement SqlCacheDependency and while adding the dependency through Stored Procedure it remove the object immediately after adding it to cache.It gives CacheItemRemovedReason as 4.

It shows the same behavior if we select all columns of a table (using * instead of using columns name) for query that selects the data on which the key is dependent, it start popping item out of cache immediately after inserting.

We have used the Database Synchronization sample provided in mentioned below link.

http://www.alachisoft.com/resources/samples/

SQL Server Version : Microsoft SQL Server 2008 R2 (SP2)

Alachisoft Edition : Enterprise Trial Version

Alachisoft SDK version : 4.9.1.1

Kal-Alachisoft commented 5 years ago

Hi Nofal,

Please note that an item that is added in NCache with SQLDependency will automatically get removed if the provided SQLDependency Query is invalid. There are a few limitations imposed by SQL Broker that need to be addressed when you are creating a query for SQLDependency. The reason why your item gets immediately removed from cache upon insertion might be because your SQLDependency Query does not fulfill the requirements provided by Microsoft.

I am sharing the link to Microsoft's official document for SQLDependency Query creation. Please reconstruct your query based on these rules and it should fix your NCache SQLDependency issue.

Supported SQL Statements for SQLDependency: https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms181122(v=sql.105)