9

I need to troubleshoot an issue I am having, and I need some help understanding how sp_msforeachdb works in order to overcome my issue.

What happens is every time I run sp_msforeachdb, I get an error Msg 102, Level 15, State 1, Incorrect syntax near '61'

An example of my code is as follows:

EXEC SP_msforeachdb 'SELECT ''?'' AS Database
                     FROM ?.sys.objects
                     WHERE name like ''%aetna%''

However, it does not matter what query I have as a parameter to sp_msforeachdb. Every time I get the same error. I do have a database that starts with '61s1d', so that makes me think it has an issue with the DB name, but I honestly do not know what is going behind the scenes on sp_msforeachdb.

Things to note.

  • It is the only database that starts with a number
  • I can try to use code like "If database is like '%61%' don't do ......", but still the same error.
  • I cannot test changing the database name--too many things connected to it.
  • If I create a test db that starts with '51', then I also get the error for that database

How can I overcome this?

14

First of all don't use sp_msforeachdb it has several known issues. You are better off using Aaron Bertrand's version.

However it uses a cursor, dynamic SQL and a replace. You can actually look at the code by using sp_helptext.

EXEC sp_helptext sp_msforeachdb

If you use this code it will fix some of your issues.

EXEC SP_msforeachdb 'SELECT ''?'' AS Database
                 FROM [?].sys.objects
                 WHERE name like ''%aetna%''

The brackets will the specific problem you mentioned. You will however run into issues if you have a database with [ or ] in it.

  • Interesting, using brackets around the "?" in the from did work. I will look into Aaron Bertrand's version. Have you noticed a significant degradation of performance because of the cursor? – Jeff.Clark Sep 12 '16 at 18:20
  • 2
    Cursors are only a real performance problem when you have more than a few rows. Unless you were getting close to the max # of databases allowed you wouldn't see an issue. The problem is that it can skip databases. Read Aaron's article and he goes into more detail. – Kenneth Fisher Sep 12 '16 at 18:22
  • 1
    I've seen it skip DBs completely on more than one occasion. Use Aaron's, it's much more reliable. – Kris Gruttemeyer Sep 12 '16 at 18:25
11

Not disagreeing with anything that @Kenneth said, I should point out that the error you are encountering has nothing to do with sp_MSForEachDB. It is due to how the Database was named: it starts with a number. The rules for naming objects (not just Databases) are detailed in the MSDN page for Database Identifiers. If you follow the "Rules for Regular Identifiers" then you don't need to enclose those names in square brackets or double-quotes. But names that fall outside of those rules do need to be enclosed (always).

  • 1
    Thank you for this info also--I was unaware of that restriction. Unfortunately that database was made a decade before I joined the company :) – Jeff.Clark Sep 12 '16 at 18:46
  • @Jeff.Clark I wasn't suggesting that you rename the DB as you mentioned in the question that you can't change it. I just wanted you (and others) to know the actual issue since it will come up again anytime you need to specify that Database name anywhere. And reading through that full set of rules can help you avoid the other things that will require a name -- Database, Table, Column, Index, Constraint, etc -- to always be enclosed in square-brackets or double-quotes. – Solomon Rutzky Sep 12 '16 at 18:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.