SQL Server – How sp_msforeachdb Works Behind the Scenes

sql serverstored-procedurest-sql

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?

Best Answer

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

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.

Related Solutions

How to Get @@SERVERNAME from Linked Server in SQL Server

You can use OPENQUERY as explained in MSDN to get the information of Linked server:

i.e using OPENQUERY ( linked_server ,'query' )

with something like below will give you server name using sys.dm_exec_connections dmv

SELECT * FROM OPENQUERY (
         linkedservername, 
         'SELECT
             @@SERVERNAME AS TargetServerName,
             SUSER_SNAME() AS ConnectedWith,
             DB_NAME() AS DefaultDB,
             client_net_address AS IPAddress
          FROM
              sys.dm_exec_connections
          WHERE
              session_id = @@SPID
        ')

SQL Server – How to Pass Table Name as an Argument

As the error states:

Unclosed quotation mark

Your string is:

'SQLCMD -S ICB3110\SQLEXPRESS -d teena -E -Q "SELECT * FROM "'+@TableName+' -o d:\processing\'+@TableName+'.csv'

Once the @TableName variable is replaced with its value, the string gets rendered as:

'SQLCMD -S ICB3110\SQLEXPRESS -d teena -E -Q "SELECT * FROM "AIR_LINE -o d:\processing\AIR_LINE.csv'

If you look at the -Q parameter, it is malformed as:

"SELECT * FROM "AIR_LINE

You just need to move the double-quote (") to the other side of the variable (it should be to the left of the -o, not to the right of the FROM):

'SQLCMD -S ICB3110\SQLEXPRESS -d teena -E -Q "SELECT * FROM '+@TableName+'" -o d:\processing\'+@TableName+'.csv'

Best Answer

Related Solutions

How to Get @@SERVERNAME from Linked Server in SQL Server

SQL Server – How to Pass Table Name as an Argument

Related Question