Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

invalid column name using sp_msforeachdb

Posted on 2011-03-12
14
Medium Priority
?
1,564 Views
Last Modified: 2012-05-11
Hi
I'm having a problem with sp_msforeachdb throwing an error "Invalid column name 'USERCREATED'."
For the purpose of example, I've stripped back what I'm actually trying to achieve to this:

execute sp_msforeachdb 'if ((''?'' LIKE ''%CoLive'') or (''?'' LIKE ''%CoData''))
BEGIN
USE [?]
print ''?''
select ''?'' AS DBNAME,USERID,USERCREATED FROM USERS
END
'
Running this in SSMS the Results tab includes all three columns for both databases that match the LIKE selection. In the Messages tab I get the aforementioned error.

Thanks
ResultsTabs.png
MessagesTab.png
0
Comment
Question by:JeremyLloyd
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 6
14 Comments
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35116142
Try this approach:

execute sp_msforeachdb '
USE [?]
IF ((DB_NAME() LIKE ''%CoLive'') OR (DB_NAME() LIKE ''%CoData''))
BEGIN
PRINT ''?''
SELECT ''?'' AS DBNAME,USERID,USERCREATED FROM USERS
END
'

Open in new window

0
 

Author Comment

by:JeremyLloyd
ID: 35116238
Sorry, no, it still doesn't work. I also tried replacing the other instances of '?' with DB_NAME()
0
 

Author Comment

by:JeremyLloyd
ID: 35116276
Here's 2 interesting things.

1. Without the USERCREATED column the query runs fine (note ONLY the DemoDataV90CoData and DemoDataV90CoLive databases are ever listed)

2. I have a MSRS database "ReportServer". This happens to have a Users table and a UserId column. I took the database offline and the query ran without errors.

There are plenty of other databases loaded and they don't have a Users table at all.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35116280
What is the result set of following query?
USE DemoDataV90CoLive
SELECT * FROM sys.columns
where [object_id] = OBJECT_ID('USERS')

Open in new window

0
 

Author Comment

by:JeremyLloyd
ID: 35116292
0
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35116311
Sorry, my mistake. I put wrong code, 'cose of my wife ;)
Try this:

execute sp_msforeachdb '
USE ?
IF EXISTS (SELECT * FROM sys.columns
where [object_id] = OBJECT_ID(''USERS''))
BEGIN
SELECT DB_NAME(),* FROM sys.columns
where [object_id] = OBJECT_ID(''USERS'') AND name LIKE ''USERCREATED''
END
ELSE
PRINT CAST (DB_NAME() AS VARCHAR(100))+'' Has no such an object'''

Open in new window

0
 

Author Comment

by:JeremyLloyd
ID: 35116334
Messages:
master Has no such an object
tempdb Has no such an object
model Has no such an object
msdb Has no such an object
DemoDataV90CoSpool Has no such an object
DemoDataV90CoDocs Has no such an object
ReportServerTempDB Has no such an object
WebDAVSQLStorage Has no such an object
DemoDataV90CoForms Has no such an object
WebTestLog Has no such an object
DatabaseStats Has no such an object

Missing databases are:
DemoDataV90CoLive
DemoDataV90CoData
ReportServer
dbwithcolumn.png
0
 

Author Comment

by:JeremyLloyd
ID: 35116372
I've tried adding a Users table to another database with a mixture of column names, and it seems there's an error message for each column in the query which doesn't exist in the Users table. If there is no Users table there are no errors.

But, the whole point of the LIKE filter is to skip irrelevant databases. Even if the LIKE wasn't working properly one would expect to see every database name in the output - which one doesn't.

This is just nuts!!!
0
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35116394
Ok, try this:
execute sp_msforeachdb '
USE [?]
IF ((DB_NAME() LIKE ''%CoLive'' OR DB_NAME() LIKE ''%CoData'') AND DB_NAME() NOT LIKE ''%ReportServer%'')
BEGIN
PRINT ''?''
SELECT ''?'' AS DBNAME,USERID,USERCREATED FROM USERS
END
'

Open in new window

0
 

Author Comment

by:JeremyLloyd
ID: 35116400
Same problem. Messages are:
DemoDataV90CoLive
Msg 207, Level 16, State 1, Line 6
Invalid column name 'USERCREATED'.
DemoDataV90CoData
0
 
LVL 14

Accepted Solution

by:
Daniel_PL earned 2000 total points
ID: 35120319
This one should fix your problem ;)

DECLARE @cmd1 VARCHAR(500)
SET @cmd1='IF (''?'' LIKE ''%CoLive'' OR ''?'' LIKE ''%CoData'') EXECUTE (''USE ? SELECT DB_NAME() AS DBNAME, USERID, USERCREATED FROM USERS'')'
EXEC sp_msforeachdb @command1=@cmd1

Open in new window

0
 

Author Comment

by:JeremyLloyd
ID: 35123138
That worked, but why?
0
 

Author Comment

by:JeremyLloyd
ID: 35123145
Sorry, clicked, to quickly... Thanks for solving that. I wouldn't have figured that one out myself. What is it that's different that makes it work? Why didn't it work any way?

Thanks again
Jeremy
0
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35123275
If any of databases has users table whole query was evaluated regardless of if clause.
This approach eliminates this evaluation.

Take care,
Daniel
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question