We have several ASP.NET applications that use a sitemap which is populated via a VB.NET implementation of the SqlSiteMapProvider example found at Wicked Code. It is compiled to a DLL, then installed and referenced from the GAC on the servers and from our locals as well. It has been working in production just fine for a few months now. I should also note we are using SQL Server 2008

Today we came in and noticed in production that there was no menu items being displayed at all on the sitemap in prod for any of the applications. Dev & QA looked fine. We have not done any maintenence to SQL Server in prod in quite some time, long before we ever even implemented SQLSiteMapProvider. We also have no updated any of the web apps recently.

The first thing I did was take the prod web.config and set it to my web.config in my IDE and run the app - still the same issue. This should rule out an IIS issue since the bug was replicated on my machine as well.

Since this was a bit urgent, the next thing I did was simply insert a dummy record into the sitemap table in prod to see what would happen. Poof, like magic, the menu items were restored and all was well. I deleted out the dummy menu item and it synced without issue.

This was a bit scary though, and I'm trying to figure out why this happened so that it does not happen again in the future. Has anyone experienced this before? If you have never used SqlSiteMapProvider - it simply uses SqlDependencyCache/ ASP.NET cache to store the menu items, then if the result set of the stored proc which returns the links changes, it tells ASP.NET this happened and it re-populates the sitemap/cache...

Maybe the connection between the web server & SQL Server somehow died? And inserting a record made it 'wake up' and reconnect again? Maybe there is a timeout value or some kind of IsStillConnected() function somewhere I need to check on PageLoad to make sure the connection is still in-tact? Or maybe ASP.NET thought the stored proc was returning no values for some reason until we inserted a record into the table? Has anyone seen this issue before?

Many thanks in advance for any guidance.

EDIT: all this came from having a SET NOCOUNT ON in my stored proc. BEWARE! This is not in the MSDN docs anywhere and really screwed me for awhile!

+1  A: 

SqlDependency is not exactly a powerhouse of stability. Here are some articles that perhaps can shed some light:

Remus Rusanu
Remus, thanks for the reply. This is filled with great info. One thing I completely forgot to mention - we are using SQL Server 2008. Do you know if these issues have been fixed / become more reliable in 08? I didn't realize that SqlDependency was 'not a powerhouse of stability' as you put it... this is surprising to hear, as when researching SqlSiteMapProvider, it seemed very much like the best solution to the SiteMap problem and many had already implemented it...
There were some improvements in the Query Notifications side of the stack, the problems that cause most of the pain in my third link I think thei're solved. Mosu issues are not with Query Notifications anyway, but from SqlDependency and its 'just-in-time' infrasturcture it deploys at Start(). The problems are not realy that bad (I'm biased to see the negatives since I only hear about problems...) and there really isn't any replacement. But is important to understand *how* it works, so you know how to troubleshoot.
Remus Rusanu
come to think of it, from the description of your problem (empty map => then it got populated with records when an update occured) it seems to be a problem with the SiteMap, not with SqlDependency. SqlDependency would be at fault if the other way around happened (updates don't invalidate the cache), but what you describe sounds like a bug in the initialisation of the SqlSiteMapProvider.
Remus Rusanu
hmm.. I think you're right. The problem now is, how the heck to debug this =). It has only happened once so far, and only in prod. I'd have to have wait for the bug to appear and then step through the code as the sitemap populates... but if it happens in prod, then that's a bit risky.. so we would might have to reprodouce in qa or dev. Such a pain...

We're having the same problem. The first time a website is opened, the menu isn't showing any items. After a refresh, this problem is fixed and all items are shown. Thanks to the SqlDependency, the items are immediately shown at other pc's as well.

It used to work well... till we migrated the server to 2008 a week ago. Have you found a solution dferraro?

Martijn meijer

Check my edit ;)

all this came from having a SET NOCOUNT ON in my stored proc. BEWARE! This is not in the MSDN docs anywhere and really screwed me for awhile!