database does not exist but...

[beechill4]

Hi,

i am running a stored procedure but keep getting the following error:

Server: Msg 911, Level 16, State 1, Line 1
Could not locate entry in sysdatabases for database 'QRM_TDM_DMT_QRM'. No entry found with that name. Make sure that the name is entered correctly.

Now, this database is not in enterprise manager and is not supposed to be either.

How can i get sql server to recognise this?

Hill

 

[JayKusch]

I am not sure what you are getting at here????

The error message is telling you that the database 'QRM_TDM_DMT_QRM' DOES NOT EXISTS !!!

It seems that your store procedure is trying to connect to the database that is no longer there and is failing with the error as it should.

What is the exact problem?

Thanks

J. Kusch

 

[beechill4]

J,

the problem is that i do not reference this dbname anywhere in the script because It does not exist!!

 

[JamesLean]

I think we might have to see your SP code...

--James

 

[JayKusch]

Are you executing it by using a 2 part name of Database..Object as in

exec QRM_TDM_DMT_QRM..MySP

If not, you are definiatly executing "some" code that is referencing that database.

Thanks

J. Kusch

 

[joeythelips]

here's the code!

CREATE PROCEDURE USP_DATABASE_SIZES AS

-- DECLARES
DECLARE @DBNAME VARCHAR(40)
DECLARE @STRING VARCHAR(300)

-- STORES EACH DATABASE NAME IN A TEMP TABLE
CREATE TABLE #DATABASE_NAMES
(
DBNAME VARCHAR (50)
)

INSERT INTO #DATABASE_NAMES
SELECT NAME FROM MASTER..SYSDATABASES
WHERE NAME NOT IN ('MASTER', 'MODEL', 'MSDB', 'NORTHWIND', 'TEMPDB')

-- TEMP TABLE FOR DATABASE SIZING INFO
CREATE TABLE #DBSIZES
(
FILE_TYPE VARCHAR(10),
TOTAL_SPACE INT,
SPACE_USED INT,
FREE_SPACE INT,
DBNAME VARCHAR(50),
DATE_RUN DATETIME DEFAULT GETDATE()
)


-- MAIN SECTION --> LOOP THROUGH ALL OF THE DATABASES AND GET SPACE INFO

-- CURSOR FOR GETTING DATABASE NAMES
DECLARE CURDATABASE CURSOR FOR SELECT DBNAME FROM #DATABASE_NAMES
OPEN CURDATABASE
FETCH NEXT FROM CURDATABASE INTO @DBNAME
WHILE @@FETCH_STATUS = 0
BEGIN


-- INSERTING SPACE INFO INTO TEMP TABLE

SELECT @STRING = 'USE ' + @DBNAME + CHAR(13) + CHAR(10) + 'INSERT INTO #DBSIZES (FILE_TYPE, TOTAL_SPACE,
SPACE_USED, FREE_SPACE) SELECT FILEID, MAXSIZE,SIZE, (MAXSIZE - SIZE) FROM SYSFILES' + CHAR(13) + CHAR(10) +
'UPDATE #DBSIZES SET DBNAME = ''' + @DBNAME + ''' WHERE DBNAME IS NULL'

EXEC (@STRING)

FETCH NEXT FROM CURDATABASE INTO @DBNAME
END
CLOSE CURDATABASE
DEALLOCATE CURDATABASE


-- UPDATING THE FILE_TYPE COLUMN

UPDATE #DBSIZES SET FILE_TYPE = 'DATA FILE' WHERE FILE_TYPE = '1'
UPDATE #DBSIZES SET FILE_TYPE = 'LOG FILE' WHERE FILE_TYPE = '2'

-- CONVERTING SPACE INFO INTO MB FROM 8K PAGES.

UPDATE #DBSIZES SET SPACE_USED = (SPACE_USED*8)/1024
UPDATE #DBSIZES SET FREE_SPACE = (FREE_SPACE*8)/1024


-- RE-ARRANGING THE DATA INTO THE UTILITY..DATABASE_SIZINGS TABLE

SELECT * FROM #DBSIZES


-- CLEAN-UP : DROP THE TEMPORARY TABLES

DROP TABLE #DATABASE_NAMES
DROP TABLE #DBSIZES



GO

 

[JayKusch]

Run this in Query Analyzer JUST TO MAKE SURE a ghost record is not in there for the QRM_TDM_DMT_QRM database

SELECT NAME FROM MASTER..SYSDATABASES
WHERE NAME NOT IN ('MASTER', 'MODEL', 'MSDB', 'NORTHWIND', 'TEMPDB')



Thanks

J. Kusch

 

[JayKusch]

Then Try replacing your INSERT statement w/ this snippette and see if it flies ...


INSERT INTO #DATABASE_NAMES
SELECT NAME FROM MASTER..SYSDATABASES
WHERE NAME NOT IN ('MASTER', 'MODEL', 'MSDB', 'NORTHWIND', 'TEMPDB', 'QRM_TDM_DMT_QRM')



Thanks

J. Kusch

 

[beechill4]

Hi j,

ran SELECT NAME FROM MASTER..SYSDATABASES
WHERE NAME NOT IN ('MASTER', 'MODEL', 'MSDB', 'NORTHWIND', 'TEMPDB')
and the villainous db wasn't returned!

hill

 

[JayKusch]

did you try the modified INSERT statement I posted above?


Thanks

J. Kusch

 

[JayKusch]

I executed your SP and it created and Execute without a hitch.

How are you executing the stored procedure(SP).

Please post the EXECUTE(Exec) statement that fires off this SP.


Thanks

J. Kusch

 

[beechill4]

exec usp_database_sizes

 

[JamesLean]

Try this code and see if it helps at all:

CREATE TABLE #dbsizes (
    file_type varchar(10),
    total_space int,
    space_used int,
    free_space int,
    dbname sysname,
    date_run datetime DEFAULT GETDATE()
)

INSERT #dbsizes (file_type, total_space, space_used, free_space, dbname)
EXEC sp_msforeachdb 'SELECT CASE fileid WHEN 1 THEN ''Data file'' WHEN 2 THEN ''Log file'' END, maxsize, size, maxsize - size, ''?'' FROM ?..sysfiles'

SELECT * FROM #dbsizes
WHERE dbname NOT IN ('master', 'model', 'msdb', 'northwind', 'tempdb')
ORDER BY dbname

DROP TABLE #dbsizes

--James

 

[beechill4]

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'TEST'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '.'.

(18 row(s) affected)


(8 row(s) affected)

 

[JamesLean]

Do you have a db with a space in the name? Try adding brackets around the db name in this line:

EXEC sp_msforeachdb 'SELECT CASE fileid WHEN 1 THEN ''Data file'' WHEN 2 THEN ''Log file'' END, maxsize, size, maxsize - size, ''?'' FROM [red][[/red]?[red]][/red]..sysfiles'

--James

 

[beechill4]

james, there are 2 databases with a space in the name. could this be the problem overall?

 

[JamesLean]

May well cause a problem with my code. Did you try my revised script?

--James

 

[JamesLean]

As a general rule it is not usually advisable to have spaces in database names, or any other object names for that matter. You should also avoid using SQL reserved words for object names.

--James

 

[beechill4]

i have just raised that with the system vendors as a concern

Thanks.

Joe

 

[beechill4]

SELECT @STRING = 'USE [' + @DBNAME + ']'

hi,

i got around the problem by replacing part of my code with the line above,

Thanks to everyone for your help.

Its much appreciated.

Joe