有什么方法可以获取 SQL Server 中打开/分配的游标列表吗?
我有一个创建并打开一些游标的存储过程。 它在最后关闭它们,但如果遇到错误,这些游标将保持打开状态! 然后,当它尝试创建游标时,后续运行将失败,因为具有该名称的游标已存在。
有没有办法可以查询存在哪些游标以及它们是否打开,以便我可以关闭并取消分配它们? 我觉得这比盲目尝试关闭和吞下错误要好。
I have a stored procedure that creates and opens some cursors. It closes them at the end, but if it hits an error those cursors are left open! Then subsequent runs fail when it tries to create cursors since a cursor with the name already exists.
Is there a way I can query which cursors exists and if they are open or not so I can close and deallocate them? I feel like this is better than blindly trying to close and swallow errors.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(5)
这似乎对我有用:
This seems to work for me:
查看此处了解有关如何查找游标的信息。 我从来没有使用过它们中的任何一个,因为我可以找到一种方法来完成它,而无需逐行地进行。
您应该重建 sp 来
不使用游标(我们可以帮助 -
几乎总有办法
避免 RBAR)
在事务中构建它,并在出现故障或检测到错误时回滚。 这里有一些关于这方面的优秀文章。 第 1 部分 和 第 2 部分
如果您有 SQL2005,还可以使用 try catch
编辑(回应您的帖子):理想情况下,数据生成最好在应用程序级别处理,因为它们更适合基于非集合的操作。
Red Gate 有一个我以前用过的 SQL 数据生成器(它的非常适合单个表,但如果您有大量 FK 或广泛的[标准化]数据库,则需要一些配置)。
Look here for info on how to find cursors. I have never used any of them because I could figure out a way to get it done without going Row By Agonizing Row.
You should rebuild the sp to either
not use cursors ( we can help -
there is almost always a way to
avoid RBAR)
build it in a transaction and roll it back if there is a failure or if you detect an error. Here are some excellent articles on this. part 1 and part 2
If you have SQL2005, you can also use try catch
EDIT (in response to your post):Ideally, data generation is best handled at the application level as they are better suited for non set based operations.
Red Gate has a SQL Data generator that I have used before (its great for single tables, but takes some configuring if you have lots of FK or a wide [normalized] database).
这适用于 2008R2,尚未在更早的版本上进行过测试:
This works on 2008R2, haven't tested on anything earlier than that:
您可以使用
如上所述 此处
基本上,您可以运行此示例查询并获取有关在各种数据库中打开的游标的信息
You can use
as described here
Basically you can run this sample query and get information about the cursors that are open in various databases
(来自 http://msdn.microsoft.com/ it-it/library/aa172595(v=sql.80).aspx )
(from http://msdn.microsoft.com/it-it/library/aa172595(v=sql.80).aspx )