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'.
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.