SQL Server – IF Statement Not Skipping TempDB When Looping Through Databases with sp_MSForEachDB

sql serversql-server-2012tempdb

[SQL Server 2012 SP2 EE]

Why does the following script gives me an error relating to tempdb?

    exec sp_MSForEachDB '
    IF ( (select database_id from sys.databases where name = ''?'') > 4)
    BEGIN 
    ALTER AUTHORIZATION ON DATABASE::? TO [sa];
    ALTER DATABASE [?] SET RECOVERY SIMPLE;
    END'

Here is the error I get:

  Msg 5058, Level 16, State 1, Line 5
  Option 'RECOVERY' cannot be set in database 'tempdb'.

It does the job it's supposed to do. But I am unable to think of a reason for the error. I know that the tempdb's databaseID is 2, then at least it should not try to set the option for tempdb.

Best Answer

NOTE TO READERS: Please read the entire question, including the example code (i.e. not just the title). This question is not about how to best cycle through databases, nor is it about why does [tempdb] receive this error. The O.P. is already trying to avoid executing the ALTER statements on all system databases (well, the 4 visible ones) and is asking why the IF statement that should be skipping [tempdb] does not appear to be skipping it.

Why does the following script gives me an error relating to tempdb?

The reason is that the IF statement only affects what happens when the code is actually running, but SQL Server still has to parse and compile the batch prior to executing it. Parse errors are ones related to syntax, such as making sure that SQL statements are properly formed and that variables have been properly declared:

-- parse the following by hitting Control-F5 or clicking the check mark in SSMS
SELECT @Bob;

gets the following error:

Msg 137, Level 15, State 2, Line 2
Must declare the scalar variable "@Bob".

And the following:

-- parse the following by hitting Control-F5 or clicking the check mark in SSMS
CREATE TABLE b

gets the following error:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'b'.

If the batch parses successfully, then it is compiled, at which time things like permissions are checked and some other checks are performed.

-- parse the following by hitting Control-F5 or clicking the check mark in SSMS
IF (1 = 0)
BEGIN 
  ALTER AUTHORIZATION ON DATABASE::[tempdb] TO [sa];
  ALTER DATABASE [tempdb] SET RECOVERY SIMPLE;
END;

The above SQL is properly formed so the batch parses successfully. Now try to execute the above SQL by hitting F5 or Control-E or the ! Execute button, etc.

This time you get the following error:

Msg 5058, Level 16, State 1, Line 4
Option 'RECOVERY' cannot be set in database 'tempdb'.

even though the IF (1 = 0) ensures that the code will never run. This means that you are running into a compilation error. You can get around these types of errors by moving the offending code into a subprocess via an EXEC call.

Execute the following and it will complete successfully since what is inside of the EXEC() is not parsed or compiled until that statement is executed at runtime.

IF (1 = 0)
BEGIN
  EXEC('
    ALTER AUTHORIZATION ON DATABASE::[tempdb] TO [sa];
    ALTER DATABASE [tempdb] SET RECOVERY SIMPLE;
  ');
END;

To summarize:
First, consider that sp_MSForEachDB cycles through the databases and executes your passed-in SQL after replacing the ? with the current database name. So when the cursor inside of sp_MSForEachDB gets to [tempdb], it effectively does the following:

IF ( (select database_id from sys.databases where name = ''tempdb'') > 4)
BEGIN 
  ALTER AUTHORIZATION ON DATABASE::tempdb TO [sa];
  ALTER DATABASE [tempdb] SET RECOVERY SIMPLE;
END

Second, there are several steps that SQL Server takes when you execute a query batch:

  1. Parse
  2. Compile
  3. Actual Execution

It is important to understand that these are separate steps and can generate errors before proceeding to the next step (and hence cancel further processing before going on to that next step). In the case here, the error is happening in Step 2 -- Compile -- as proven in the 2nd to last example above (the first one to start with IF (1 = 0)). The IF (1 = 0) prevents the code inside of the BEGIN...END block from ever running, yet the error still occurs. Hence the error is not happening due to an actual attempt to run the two ALTER statements.

The reason that wrapping the ALTER statements inside of the EXEC() function works is because SQL Server will not parse and compile what is inside of the EXEC() until the EXEC() is actually running. At that point, the IF ( (select database_id from sys.databases where name = ''?'') > 4) statement will be allowed to run since the batch won't fail during Compilation and will make it to Execution, and the IF statement will skip over [tempdb] and the "Option 'RECOVERY' cannot be set in database 'tempdb'" error will not occur.

Related Question