0

I am running a query with the sp_MSforeachdb stored procedure. I used three databases which exist in my server instance: ExcelineDev, EXCE_NR1, and EXCE_AKI. 

declare @t table (dbname varchar(50),ss varchar(max),sss varchar(max))
DECLARE @command varchar(max)

SELECT @command = 'IF ''?'' IN (''ExcelineDev'',''EXCE_NR1'',''EXCE_AKI'') 
BEGIN USE ? 
EXEC
(''
select DB_NAME(DB_ID()), category,name from usrpt.report
where name like ''''%leders%''''
'')
END'

print @command
insert into @t
EXEC sp_MSforeachdb @command
select * from @t

I'm getting the following error:

'Msg 911, Level 16, State 1, Line 2 Database 'Migration_Release_1' does not exist. Make sure that the name is entered correctly.'

I'm confused, because there is no DB called 'Migration_Release_1' in my server instance. Why is SQL searching for this database?

AHiggins
  • 7,029
  • 6
  • 36
  • 54
Kasun Sameera Madhusanka
  • 132
  • 2
  • 12
  • we are not passing a variable there..it acts like the DB variable – Kasun Sameera Madhusanka May 14 '15 at 13:26
  • run this: `Select name from master.dbo.sysdatabases`. Is there any entry for Migration_Release_1 in that result set? – DeadZone May 14 '15 at 13:52
  • no..i run that and checked...i think it's due to deleting Migration_Release_1 DB without closing all the connections... – Kasun Sameera Madhusanka May 15 '15 at 11:33
  • My suggestion would be to pull up the text of the sp_MSForEachDB procedure and manually walk through it until you figure out which query is picking up that database value. Perhaps the query results are cached and [clearing the server's buffers](http://stackoverflow.com/questions/1873025/how-can-i-clear-the-sql-server-query-cache) might eliminate the problem. – DeadZone May 15 '15 at 14:58

0 Answers0