存储过程超时 - 但从 SSMS 运行时正常
我有一个存储过程，错误提示“超时已过期”。
涉及的代码是ADO/VB6。
存储过程本身没有问题，您可以在查询窗口中运行它，并且需要不到一秒钟的时间。
用于获取连接等的代码也是模块化的，并在大型应用程序中使用。 在一个特定数据库上，仅在这一位置发生超时。
无论是否在调试中运行 VB6 代码，每次尝试数百次都会重现该错误，然后突然一切都会神奇地再次开始工作。 那么在未来的某个时候，同样的问题还会再次出现。
我不确定要在这里放置多少代码，这并不复杂； 基本上是；
Set adoCommandObject.ActiveConnection = ...{open ADODB.Connection object}
Set rs = CreateObject("ADODB.Recordset")
Call rs.Open(adoCommandObject, , adOpenForwardOnly, adLockReadOnly)'Timeout occurs here
我一直在探查器中观察，但没有给出任何线索，除了偶尔看到 sp 运行之前和之后发生的“SET NO_BROWSETABLE ON”/“SET NO_BROWSETABLE OFF”语句。
我在网上搜索过，但未能找到任何令人满意的帮助； 此时我愿意尝试任何事情（除了在 .NET 中重写，不幸的是这不是一个选择！）
I have a stored procedure which is erroring with "Timeout expired".
The code involved is ADO/VB6.
The stored procedure itself is not a problem, you can run it in a query window and it takes less than a second.
The code used to get the connection etc is also modularised and in use all over a huge application. It is only in this one place that the timeout occurs, on one particular database.
The error will be reproducable every time for hunderds of attempts, whether running the VB6 code in debug or not, then suddenly everything will magically start working again. Then some time in the future the same problem will show up again.
I'm not sure how much code to put here, there's nothing complex about it; it's basically;
Set adoCommandObject.ActiveConnection = ...{open ADODB.Connection object}
Set rs = CreateObject("ADODB.Recordset")
Call rs.Open(adoCommandObject, , adOpenForwardOnly, adLockReadOnly)'Timeout occurs here
I've been watching in profiler but that hasn't given any clues, except for occasionally seeing "SET NO_BROWSETABLE ON" / "SET NO_BROWSETABLE OFF" statements occurring before and after the sp runs.
I've searched the net but not been able to find any satisfactory help for this; I'm willing to try anything at this point (except rewrite in .NET, unfortunately that's not an option!)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我认为你想得太多了。无意冒犯，但如果你使用 MSSQL，那么就像有人打开查询窗口并绑定数据库一样简单。 这很容易测试。
我以前也遇到过同样的麻烦。 我之前运行过没有超时的存储过程，通常会立即运行，但会静置过夜而不运行。 结果发现另一名员工打开了查询窗口。 关上窗户，噗的一声，它终于运行了。
检查一下，您会惊讶于表锁可以对您的应用程序执行什么操作。
我这样说是因为你说这个问题是间歇性的。 它来了又走。 我怀疑是表锁。 无论是应用程序执行此操作，还是由另一个用户对数据库进行查询来执行此操作。 如果不是其他用户，请检查以确保您的应用程序在每次使用数据库时都关闭与数据库的连接。
I think you're over thinking this.No offense, but if you are using MSSQL, it's as simple as someone leaving a query window open and it ties up the database. This is easily tested.
I've had the same trouble before. I've run stored procedures before that had no timeout, that normally would run immediately but would sit overnight and not run. Only to find out another employee left their query window open. Close their window and poof it finally runs.
Check this out, you'd be suprised what a table lock can do to your application.
I say this because you said that the problem is intermitten. It comes and goes. I suspect a table lock. Whether it be the application doing it, or it is being done by another user making queries to the DB. If it's not another user, then check to make sure your application is closing connections to the DB each time their being used.
如此处所述，SET NO_BROWSETABLE ON类似于使用用于选择中的浏览。 我猜它是由 ado 认为您可能想要更新该记录集时自动生成的。 您可能可以设置记录集的一个属性来阻止这些记录集的发布，但这似乎不太可能是问题所在。
As described here SET NO_BROWSETABLE ON is similar to using FOR BROWSE in selects. I guess it's auto-generated by ado when it thinks you might want to update that record set. There's probably a property of the Recordset you could set to stop those being issued, but it seems unlikely that's the problem.