SQL Server – Resolving ‘Database Does Not Exist’ Error Message

sql server

Hoping someone can help out here. I've searched for an answer on my own as much as I could in the areas that I understand regarding SQL Server administration. Something which I'm fairly new at. ( Under 2 years experience )

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

The issue is when running this script (error above) ( script posted below ) it stops and says it's not able to hit a particular database as if its still connected. However, this database which was from a migration from old server to new and was renamed (all areas including files) no longer exists anywhere. I do not see it in any T-SQL outputs or any reports and the MDF / LDF files are long gone. However, the script still hangs and thinks this DB should be part of this instance.

In searching, I've come across various posts stating a possible permission issue and to check the master DB for residual information all of which have not panned out with any solution. I have full access under my active directory login for this instance and have the SA codes as well. Same error when running as that.

Any help would be appreciated, hopefully, its something simple and I'm just missing something obvious.

Thank you all for your help, it's appreciated.

DECLARE @CurrentDB sysname
DECLARE curDatabase CURSOR FAST_FORWARD FOR 
SELECT name FROM master.sys.databases 
--SELECT * FROM master.sys.databases 
WHERE name not in ('master','tempdb','msdb','model','AdventureWorks','AdventureWorksDW')
OPEN curDatabase
FETCH NEXT FROM curDatabase INTO @CurrentDB
WHILE ( @@FETCH_STATUS = 0)
BEGIN
--print @CurrentDB
--
declare @sql1 varchar(100)
select @sql1 = 'USE ' + @CurrentDB + ';' + '

'

--EXEC sp_sqlexec @sql1
--print @sql1

declare @sql2 varchar(max)
select @sql2 = 
-- Ensure a USE <databasename> statement has been executed first.
'
SET NOCOUNT ON

SELECT DB_NAME() AS DataBaseName;


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''#work_to_do'') AND type in (N''U''))
DROP TABLE #work_to_do


SELECT
    object_id AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, ''LIMITED'')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;


select rtrim(name) as objectname,frag as frag_percent,type,create_date,objectid from #work_to_do a , sys.objects b
where a.objectid=b.object_id
order by frag desc
'


declare @sql varchar(max)
select @sql = @sql1 + @sql2

EXEC sp_sqlexec @Sql

--print @sql




FETCH NEXT FROM curDatabase INTO @CurrentDB
END -- curDatabase WHILE loop
CLOSE curDatabase
DEALLOCATE curDatabase

Best Answer

Using your script, I hit the same error with a database called ಠ_ಠ.

I've made some changes to handle Unicode strings properly, and quote any odd characters in database names.

Ultimately, the problem was with your use of sp_sqlexec. When I changed it to sp_executesql, the script worked fine.

DECLARE @CurrentDB NVARCHAR(128);
DECLARE curDatabase CURSOR FAST_FORWARD FOR
    SELECT name
    FROM   master.sys.databases
    --SELECT * FROM master.sys.databases 
    WHERE  name NOT IN ( 'master', 'tempdb', 'msdb', 'model', 'AdventureWorks', 'AdventureWorksDW' );
OPEN curDatabase;
FETCH NEXT FROM curDatabase
INTO @CurrentDB;
WHILE ( @@FETCH_STATUS = 0 )
    BEGIN
        --print @CurrentDB
        --
        DECLARE @sql1 NVARCHAR(MAX) = N'';
        SELECT @sql1 = @sql1 + N'USE ' + QUOTENAME(@CurrentDB) + N';

'       ;

        --EXEC sp_sqlexec @sql1
        --print @sql1

        DECLARE @sql2 NVARCHAR(MAX) = N'';
        SET @sql2
            = @sql2
              +
        -- Ensure a USE <databasename> statement has been executed first.
        N'
SET NOCOUNT ON

SELECT DB_NAME() AS DataBaseName;


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''#work_to_do'') AND type in (N''U''))
DROP TABLE #work_to_do


SELECT
    object_id AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, ''LIMITED'')
WHERE 1 = 0;


select rtrim(name) as objectname,frag as frag_percent,type,create_date,objectid 
from #work_to_do a , sys.objects b
where a.objectid=b.object_id
order by frag desc
'       ;

        --PRINT @sql2


        DECLARE @sql NVARCHAR(MAX) = N'';
        SET @sql = @sql + @sql1 + @sql2;

        PRINT @sql;
        EXEC sys.sp_executesql @sql;

        SET @sql = N'';
        SET @sql1 = N'';
        SET @sql2 = N'';


        FETCH NEXT FROM curDatabase
        INTO @CurrentDB;
    END; -- curDatabase WHILE loop
CLOSE curDatabase;
DEALLOCATE curDatabase;

Also, I'd strongly recommend against writing maintenance scripts like this from scratch. Ola Hallengren writes amazing free ones.

Related Question