Inconsistent cursor results when navigating through databases

Question

I have several databases with very similar ones (my-db-1, my-db-2, my-db-3, my-db-4). I want to execute the same stored procedure in each of these databases. I decided to use cursors. However, I am having some strange problems. First of all, this is my simple code that I execute 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 

Performing this several times in a row for 2 seconds, I get strange results:

Execution1:

 my-db-1 my-db-2 my-db-3 my-db-4 

Execution2:

 my-db-1 my-db-2 

Execution3:

 my-db-1 my-db-2 my-db-3 my-db-4 

Execution4:

 my-db-1 

It seems to be completely random. Sometimes I will receive all 4 databases for printing after 10 executions. Sometimes after only 2 executions, only one database will be printed.

This SQL runs on Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) April 2, 2010 15:48:46 Copyright (c) Microsoft Developer Developer Edition (64-bit) on Windows NT 6.1 (Build 7600 :) via Microsoft SQL Server Management Studio 10.50.1600.1

Does anyone have any ideas?

Answer 1

Try declaring your cursor as FAST_FORWARD .

By default, an updatable cursor is used, and these mandatory update locks are likely to conflict with another process accessing sys.databases

Ref .: DECLARE CURSOR