February 5, 2016 at 4:20 pm
DECLARE @cmd1 VARCHAR(MAX)
SET @cmd1='IF(''?'' LIKE ''SLX_%'')
BEGIN
IF OBJECT_ID(''[?].dbo.employee'',''U'') IS NOT NULL
BEGIN
INSERT INTO SLX..TAB1([DB_NAME],ID,[COUNT])
SELECT DB_NAME(),id,COUNT(*) FROM [?].dbo.employee WHERE dt_term IS NULL AND id > 0
GROUP BY id HAVING COUNT(*) > 1
INSERT INTO SLX..TAB2([DB_NAME],ID,NO,[COUNT])
SELECT DB_NAME(),id,no,COUNT(*) FROM [?].dbo.employee WHERE dt_term IS NULL AND id > 0
GROUP BY id,no HAVING COUNT(*) > 1
END
END'
PRINT @cmd1
EXEC sp_msforeachdb @command1=@cmd1
I'm getting following error
Msg 207, Level 16, State 1, Line 6
Invalid column name 'id'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'id'.
Msg 207, Level 16, State 1, Line 6
Invalid column name 'id'.
Msg 207, Level 16, State 1, Line 9
Invalid column name 'id'.
Msg 207, Level 16, State 1, Line 10
Invalid column name 'id'.
Msg 207, Level 16, State 1, Line 9
Invalid column name 'id'.
If I were to replace the [?] with any database name that starts with SLX_%, it works great.
February 5, 2016 at 9:56 pm
I guess my first question is about the TAB1 and TAB2 tables you're inserting into. Do they both have an ID column?
The next one would be about all your databases that match SLX%. Does the employee table in each one have an ID column?
Because the errors you posted are using the lower case "id" instead of the upper case ones you used in the column list for the insert, my guess is that you have a database out there that starts with SLX that contains an employee table without an id column.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply