Monday, October 24, 2011

DBREINDEX maintenance job fails with error “Could not find database 'SQLTester' ". But SQLTester is actually a SCHEMA!!!

I have configured a job to perform reindexing operation on all tables of 'AdventureWorks' database. When I ran job it went fine for few minutes and thrown below error:

Msg 2520, Level 16, State 5, Line 16
Could not find database 'SQLTester'. The database either does not exist, or was dropped before a statement tried to use it. Verify if the database exists by querying the sys.databases catalog view.

On verifying, noticed there is no database named ‘SQLTester’ in the server but unfortunately the job is failing for the database AdventureWorks. So I ran the query (mentioned below)which I am using in the job step to display all the table names along with schema name

SELECT TABLE_SCHEMA+’.’+TABLE_NAME FROM information_schema.tables
WHERE table_type = 'BASE TABLE' AND TABLE_CATALOG = 'AdventureWorks' and
TABLE_NAME IN (SELECT name from sysobjects where xtype = 'U')

Here in the output I noticed a table which under a SCHEMA named ‘SQLTester’. I REALLY DON’T KNOW WHY SQL IS GIVING ERROR MESSAGE AS ‘CANNOT FIND DATABASE’ if it is actually a SCHEMA. Just tried changing the query (mentioned below) by adding ‘[‘ to the schema names.

SELECT '['+TABLE_SCHEMA+'].'+TABLE_NAME FROM information_schema.tables
WHERE table_type = 'BASE TABLE' AND TABLE_CATALOG = 'AdventureWorks' and
TABLE_NAME IN (SELECT name from sysobjects where xtype = 'U')

When I ran the re-indexing operation with above modified query it worked find even for the tables that are under the schema SQLTester. Now the job is running fine.

No comments:

Post a Comment