Perry Whittle (9/16/2008)
sunshine (9/15/2008)
the previous statement must be terminated with a semicolon.this message is fairly descriptive!! The USE statement and exec must be separated by a semi colon like so
That message is just saying that if the with is starting a CTE (which in this case it is not) the previous command needs to be terminated with a ;
It's a generic message that is given any time there's a syntax error and the keywork 'with' is anywhere nearby. In this case, it's misleading as the problem has nothing to do with the 'with'
In this case, the problem is with the single quotes. Change the select to the following and it works (in my 2008 test instance)
SELECT @execmd = 'USE ' + @dbname + ' Exec sp_MSForEachTable ''Update Statistics ? with FULLSCAN'''
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability