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
2 Answers 2
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.
I faced such problem when running a script in SQL Server Management Studio to do some stuff on my Local SQL Server. I noticed that the Query Window that I was running the script in was connected to a Remote SQL Server not my local machine.