Database doesn't exist but query keeps looking for the database

Question

Database doesn't exist but query keeps looking for the database

PJ_SQL

SSCertifiable

Points: 6703
More actions
January 3, 2019 at 10:00 am

#409408

I am running query for all the databases in the instance using sp_MSforeachdb and it keeps erroring out with
Database 'DBName' does not exist. Make sure that the name is entered correctly.
That DBName database doesn't exist in the instance.　
Thank you.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

PJ_SQL - Thursday, January 3, 2019 10:00 AM
I am running query for all the databases in the instance using sp_MSforeachdb and it keeps erroring out with
Database 'DBName' does not exist. Make sure that the name is entered correctly.
That DBName database doesn't exist in the instance.ã€€
Thank you.

Well, since we can't see the code that you are running how do you expect us to help?

PJ_SQL SSCertifiable Points: 6703 More actions · Answer 2

Even if I tried excluding in NOT in still giving the same error

DECLARE @command1 varchar(1000)

SELECT @command1 = 'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') BEGIN USE ?

IF EXISTS (SELECT * FROM sys.database_audit_specifications WHERE name = N''DatabaseAuditSpecification'')

BEGIN

ALTER DATABASE AUDIT SPECIFICATION DatabaseAuditSpecification WITH (STATE = OFF)

DROP DATABASE AUDIT SPECIFICATION DatabaseAuditSpecification

END

CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification]

FOR SERVER AUDIT [InstanceAudit]

ADD (EXECUTE ON DATABASE::? BY [name])

WITH (STATE = ON)

END'

EXEC sp_MSforeachdb @command1

rodjkidd SSCoach Points: 15916 More actions · Answer 3

Do any of your databases have spaces or odd characters in their names?

Change BEGIN USE ? in the select @command1 = ...
to
BEGIN USE [?]

to get around that.

Cheers,

Rodders...

Sue_H SSC Guru Points: 90860 More actions · Answer 4

PJ_SQL - Thursday, January 3, 2019 10:09 AM
Even if I tried excluding in NOT in still giving the same error
DECLARE @command1 varchar(1000)
SELECT @command1 = 'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') BEGIN USE ?
IF EXISTS (SELECT * FROM sys.database_audit_specifications WHERE name = N''DatabaseAuditSpecification'')
BEGIN
ALTER DATABASE AUDIT SPECIFICATION DatabaseAuditSpecification WITH (STATE = OFF)
DROP DATABASE AUDIT SPECIFICATION DatabaseAuditSpecification
END
CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification]
FOR SERVER AUDIT [InstanceAudit]
ADD (EXECUTE ON DATABASE::? BY [name])
WITH (STATE = ON)
END'
EXEC sp_MSforeachdb @command1

Is the error actually for the specific database DBName? If that's the case, you might want to double check the code as in your post yesterday you did have DBName hard coded in the script on the line ADD (EXECUTE ON DATABASE::DBName BY [public])

Sue