Tag Archives: @@FETCH_STATUS

For Each DB… Almost

The potential problems with the undocumented system procedure sp_MSforeachdb are well known to seasoned TSQL developers. Yes, it doesn’t pickup all databases (only those that are accessible at the time of execution). Yes, under certain versions of SQL Server (such as 2005 RTM) it picks up snapshots as well. Yes, it is a cursor behind the scenes and thus doesn’t offer any performance advantage to rolling your own.

And here is one more problem: it doesn’t trap @@FETCH_STATUS = -2.

What is the significance of that? Well, it is one more way for the procedure to let you down in a way you didn’t expect. Let’s take a closer look.

The following code will use the system procedure to list every* database on the server (*within the constraints of accessibility mentioned above! ;).

-- declare and set a sql string to print the database name
DECLARE @sql NVARCHAR(2000) = N'PRINT ''?'';';

-- use sp_MSforeachdb to print the name of "each" database
EXECUTE sp_MSforeachdb @command1 = @sql;

On my DEV server, the above query generates the following result:

master
tempdb
model
msdb
ReportServer$DEV
ReportServer$DEVTempDB
devDB
devDW

Six “system” databases plus a dev database and a dev data warehouse. So far so good. But… what if one of those databases goes offline while the procedure is running? Let’s find out!

For this example I will take the second to last database (devDB) offline during procedure execution. If you are testing at home, choose any database except a system database or the last database in your list. The system database restriction should be obvious; excluding the last database will become apparent after the demo.

-- declare and set a sql string to take the "devDB" database offline
-- and print the context database name from the MSforeachdb procedure
DECLARE @sql NVARCHAR(2000) = N'IF ''?'' = ''master''
    ALTER DATABASE devDB SET OFFLINE WITH ROLLBACK IMMEDIATE;
PRINT ''?'';';

-- use sp_MSforeachdb to execute the sql against "each" database
EXECUTE sp_MSforeachdb @command1 = @sql;

-- put "devDB" back online
ALTER DATABASE devDB SET ONLINE;

On my DEV system, when I execute the previous query the following results are returned:

master
tempdb
model
msdb
ReportServer$DEV
ReportServer$DEVTempDB

Notice anything? Not only is devDB missing from the list… but so is devDW! And yet, no execution errors reported, so I’d have no idea anything went wrong.

But, what actually did go wrong? As I mentioned, the problem is that sp_MSforeachdb does not trap @@FETCH_STATUS = -2. To demonstrate, let’s first recreate what the procedure is doing.

-- declare the cursor output variable
DECLARE @sysDBName SYSNAME;

-- declare and initialize the cursor
DECLARE crs CURSOR FOR SELECT name FROM sys.databases;

-- open the cursor
OPEN crs;

-- initialize the cursor output
FETCH NEXT FROM crs INTO @sysDBName;

-- iterate through the cursor records
WHILE @@FETCH_STATUS = 0
BEGIN
    -- take the "devDB" offline
    IF @sysDBName = 'master'
        ALTER DATABASE devDB SET OFFLINE WITH ROLLBACK IMMEDIATE;
    -- print the cursor context database
    PRINT @sysDBName;
    -- fetch the next value from the cursor
    FETCH NEXT FROM crs INTO @sysDBName;
END

-- close cursor
CLOSE crs;

-- release cursor from memory
DEALLOCATE crs;

-- put "devDB" back online
ALTER DATABASE devDB SET ONLINE;

The result of the above query is the same as the procedure; devDW is missing and no error is returned. But now we can easily add some additional syntax to identify and report on the issue. For example, add the following immediately after the FETCH command within the WHILE loop:

-- trap for missing records
WHILE @@FETCH_STATUS = -2 BEGIN
    PRINT 'The row fetched is missing. Continuing to the next row....';
    FETCH NEXT FROM crs INTO @sysDBName;
END

Now we can see that the devDB row could not be fetched because it is missing from the cursor record set. But we can also chose to continue on to the next record, or take other action as needed. Another approach could be to loop through cursor results on @@FETCH_STATUS <> -1 instead of = 0. This example also shows why we didn’t get an error record — because, despite the unexpected results, no error actually occurred! @@FETCH_STATUS -2 is not an error, but a possibility that SQL Server anticipates and has already enumerated. See here for more info.

While we don’t need any more reasons to abandon sp_MSforeachdb, I suppose it is worth remembering that the procedure is undocumented for a reason.