designGel Blog
A quick look at the world from a software developer's perspective.

ASP.NET SqlCacheDependency query "rules".

Wednesday, 9 September 2009 16:18 by Ryan

Well, I'll start by saying this: I learned this the hard way.

In other words, I spent the better part of my day, yesterday, attempting to get a SECOND table of data using the SqlCacheDependency to cache properly.

Here is what I learned about the query "rules" that don't happen to be documented well (or at all?):

DO NOT USE 'text' or 'ntext' column types in your query (possibly not in the table, either)!!!

The SQL server was dropping various errors in its log about the service broker failing to get the data (and on the front-end of the web app the data would not cache, but kept requiring a reload).  It turns out this simple query was failing due to the use of an [ntext] column field:

SELECT id, Code, Name, Value FROM LanguageEntries

The [Value] column was of type 'nText'.  I changed the column to an 'nvarchar[MAX]' type and all is well.  The service broker properly gets the data and my code properly stores it in the web cache as expected.  After debugging the server for hours (including installing service packs, setting a master key on the database (the log told me to! lol), etc) I finally gave up and created a brand new, super-simple table and noticed that it cached properly.  At that point I decided to slowly change that table to resemble my original "LanguageEntries" table and low and behold I found the problem!

So, word of the wise: if you're pulling your hair out trying to figure out why your service broker is failing to update the web app's cached data, it may be due to a simple column-type conflict. ;)