我们有几个使用站点地图的 ASP.NET 应用程序，该站点地图是通过 SqlSiteMapProvider 示例的 VB.NET 实现填充的，该示例位于 邪恶代码。它被编译为 DLL，然后从服务器上的 GAC 以及我们本地的 GAC 进行安装和引用。它已经在生产中正常运行了几个月。我还应该注意到我们正在使用 SQL Server 2008
今天我们进入生产环境并注意到在产品中的站点地图上根本没有显示任何应用程序的菜单项。开发与开发质量检查看起来不错。我们已经有一段时间没有在产品中对 SQL Server 进行任何维护了，早在我们实现 SQLSiteMapProvider 之前。我们最近也没有更新任何网络应用程序。
我做的第一件事是获取 prod web.config 并将其设置为我的 IDE 中的 web.config 并运行应用程序 - 仍然是同样的问题。这应该排除了 IIS 问题，因为该错误也被复制到了我的机器上。
由于这有点紧急，我做的下一件事就是简单地将一条虚拟记录插入到产品中的站点地图表中，看看会发生什么。噗，就像变魔术一样，菜单项恢复了，一切都很好。我删除了虚拟菜单项，它同步没有问题。
不过，这有点可怕，我正在尝试找出为什么会发生这种情况，以便将来不再发生。以前有人经历过吗？如果您从未使用过 SqlSiteMapProvider - 它只是使用 SqlDependencyCache/ ASP.NET 缓存来存储菜单项，那么如果返回链接的存储过程的结果集发生更改，它会告诉 ASP.NET 发生了这种情况，并重新填充站点地图/缓存...
也许是网络服务器和站点之间的连接SQL Server 莫名其妙死掉了？插入一条记录使它“醒来”并再次重新连接？也许有一个超时值或某种 IsStillConnected() 函数，我需要检查 PageLoad 以确保连接仍然完好无损？或者，也许 ASP.NET 认为存储过程由于某种原因没有返回任何值，直到我们将记录插入表中？以前有人见过这个问题吗？
非常感谢您的指导。
编辑：所有这一切都来自于我的存储过程中的 SET NOCOUNT ON 。提防！这在任何地方的 MSDN 文档中都没有，这确实让我困惑了一段时间！
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!
发布评论
评论（2）
SqlDependency 并不完全是稳定的动力源。以下是一些文章，也许可以提供一些线索：
SqlDependency is not exactly a powerhouse of stability. Here are some articles that perhaps can shed some light:
检查我的编辑；）
所有这些都来自于我的存储过程中的 SET NOCOUNT ON。提防！这在任何地方的 MSDN 文档中都没有，这确实让我困惑了一段时间！
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!