JeremyLloyd
asked on
invalid column name using sp_msforeachdb
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
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
ASKER
Sorry, no, it still doesn't work. I also tried replacing the other instances of '?' with DB_NAME()
ASKER
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.
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.
What is the result set of following query?
USE DemoDataV90CoLive
SELECT * FROM sys.columns
where [object_id] = OBJECT_ID('USERS')
ASKER
Attached
demodatav90colive-columns.htm
demodatav90colive-columns.htm
Sorry, my mistake. I put wrong code, 'cose of my wife ;)
Try this:
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'''
ASKER
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
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
ASKER
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!!!
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!!!
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
'
ASKER
Same problem. Messages are:
DemoDataV90CoLive
Msg 207, Level 16, State 1, Line 6
Invalid column name 'USERCREATED'.
DemoDataV90CoData
DemoDataV90CoLive
Msg 207, Level 16, State 1, Line 6
Invalid column name 'USERCREATED'.
DemoDataV90CoData
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That worked, but why?
ASKER
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
Thanks again
Jeremy
If any of databases has users table whole query was evaluated regardless of if clause.
This approach eliminates this evaluation.
Take care,
Daniel
This approach eliminates this evaluation.
Take care,
Daniel
Open in new window