循环遍历数据库时出现不一致的游标结果 [英] Inconsistent cursor results when looping over databases
问题描述
我有几个命名非常相似的数据库(my-db-1、my-db-2、my-db-3、my-db-4).我想在这些数据库中的每一个上执行相同的存储过程.我决定使用游标.但是,我遇到了一些奇怪的问题.首先是我通过 SQL Server Management Studio 2008 执行的简单代码.
I have several databases named very similar (my-db-1, my-db-2, my-db-3, my-db-4). I want to execute the same stored procedure on each of these databases. I decided to use cursors. However, I am getting some strange issues. First here is my simple code that I am executing through SQL Server Management Studio 2008.
DECLARE @db_cursor CURSOR
DECLARE @name varchar(255)
DECLARE @Sql nvarchar(4000)
SET @db_cursor = CURSOR FOR
SELECT name FROM sys.databases WHERE name LIKE 'my-db-%'
OPEN @db_cursor
FETCH NEXT FROM @db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Sql = 'Use [' + @name + ']; PRINT DB_NAME();'
exec sp_sqlexec @Sql
FETCH NEXT FROM @db_cursor INTO @name
END
CLOSE @db_cursor
DEALLOCATE @db_cursor
在 2 秒内连续执行多次,我得到奇怪的结果:
Executing this multiple times in a row within 2 seconds, I get strange results:
执行 1:
my-db-1
my-db-2
my-db-3
my-db-4
执行 2:
my-db-1
my-db-2
执行 3:
my-db-1
my-db-2
my-db-3
my-db-4
执行 4:
my-db-1
这似乎是完全随机的.有时我会在执行 10 次后打印所有 4 个数据库.有时仅执行 2 次后,只会打印 1 个数据库.
It seems like its completely random. Sometimes I'll get all 4 databases to print after 10 executions. Sometimes after just 2 executions only 1 database will get printed.
此 SQL 正在 Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) 2010 年 4 月 2 日 15:48:46 版权所有 (c) Microsoft Corporation Developer Edition(64 位)在 Windows NT 6.1(构建)上7600:) 通过 Microsoft SQL Server Management Studio 10.50.1600.1
This SQL is executing on Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.1 (Build 7600: ) through Microsoft SQL Server Management Studio 10.50.1600.1
有人有什么想法吗?
推荐答案
尝试将光标声明为 FAST_FORWARD
.
默认是一个可更新的游标,这些所需的更新锁可能与另一个访问 sys.databases
The default is an updateable cursor and these required update locks probably conflict with another process accessing sys.databases
参考:声明光标
这篇关于循环遍历数据库时出现不一致的游标结果的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!