sp_MSforeachdb MS SQL error

Question

This is for MS SQL 2005: Anyone know why the first succeeds, then creating two of the same statement in succession fails? All the statements are exactly the same. Changing the double quote to two single quotes has the same effect.

sp_MSforeachdb @command1 = 'if (left("?", 2) = "p_") begin; print "?"; end;';

produces

p_NationalBrands
p_NonBrand
p_Database_Name_That_Begins_With_P_Underbar

but

sp_MSforeachdb @command1 = 'if (left("?", 2) = "p_") begin; print "?"; end;';
sp_MSforeachdb @command1 = 'if (left("?", 2) = "p_") begin; print "?"; end;';

produces

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

Answer 1

Multiple stored proc calls in the same batch each one needs EXEC(UTE)

EXEC sp_MSforeachdb @command1 = 'if (left(''?'', 2) = ''p_'') begin; print ''?''; end;';
EXEC sp_MSforeachdb @command1 = 'if (left(''?'', 2) = ''p_'') begin; print ''?''; end;';

I'd always use 2 x single quote not double quote to avoid issues with the QUOTED_IDENTIFERS environment setting.

Answer 2

You need to explicitly use Exec when you are calling multiple procs in that manner, try

sp_MSforeachdb @command1 = 'if (left("?", 2) = "p_") begin; print "?"; end;';
exec sp_MSforeachdb @command1 ='if(left("?", 2) = "p_") begin; print "?"; end;';

from the documentation for Execute

Using EXECUTE with Stored Procedures

You do not have to specify the EXECUTE keyword when you execute stored procedures when the statement is the first one in a batch.