February 24, 2009 at 4:35 pm
Hi,
I ran upgrade adviser on sql 2000.It is telling me to change INFORMATION_SCHEMA.SCHEMATA to SYS.DATABASES in one of the procedures in database PolicyDB
So did alter the procedure by changing all INFORMATION_SCHEMA.SCHEMATA to SYS.DATABASES and executed in sql 2005. But Iam getting the following errors:
sg 207, Level 16, State 1, Procedure ABC_Utility_DB, Line 37
Invalid column name 'CATALOG_NAME'.
1.
I have the script in the procedure ABC_Utility_DB as below in sql 2000
SET @PolicyDB =
LEFT(DB_NAME(), charINDEX('_', DB_NAME())) + 'ABC_Policy' +
RIGHT(DB_NAME(), charINDEX('_', REVERSE(DB_NAME())))
IF NOT EXISTS (SELECT NULL FROM INFORMATION_SCHEMA.SCHEMATA WHERE CATALOG_NAME = @PolicyDB)
2.
I changed the script as below and ran it sql 2005
SET @PolicyDB =
LEFT(DB_NAME(), charINDEX('_', DB_NAME())) + 'ABC_Policy' +
RIGHT(DB_NAME(), charINDEX('_', REVERSE(DB_NAME())))
IF NOT EXISTS (SELECT NULL FROM SYS.DATABASES WHERE CATALOG_NAME = @PolicyDB)
Do I need to change this CATALOG_NAME to something else in sql 2005?
Please advice me
Thanks
February 25, 2009 at 11:28 am
Could you advice me how to resolve this upgrade issue
Thanks
February 25, 2009 at 11:37 am
Look up sys.databases in BOL (Books Online). If that doesn't help answer your question, let us know.
February 25, 2009 at 12:41 pm
Replace it with "Name".
Manu
February 25, 2009 at 1:38 pm
Thanks Manu..its worked....
October 9, 2009 at 12:37 am
Hi MANU,
Do I need to change this CATALOG_NAME to something else in sql 2005?
Is that right thing to change CATALOG_NAME to name??? can you please explain me why we need to change CATALOG_NAME to name?
Does changing compatibility mode 90, did not support CATALOG_NAME??
where this mentioned in BOL? In BOL its only explaining to change INFORMATION_SCHEMA.SCHEMATA sys.databases after you change compatibility mode to 90
thanks
October 9, 2009 at 2:09 pm
sg 207, Level 16, State 1, Procedure ABC_Utility_DB, Line 37
Invalid column name 'CATALOG_NAME'.
where this mentioned in BOL? In BOL its only explaining to change INFORMATION_SCHEMA.SCHEMATA sys.databases after you change compatibility mode to 90
Upgrade advisor told the OP to go from INFORMATION_SCHEMA.SCHEMATA to sys.databases because
In SQL 2000 INFORMATION_SCHEMA.SCHEMATA used to only return 1 row per DB because it didn't really implement schemas as we know them today. In 2005 the same view will return multiple rows per database (one for each schema)
When changing his query to pull data from the sys.databases view instead of the INFORMATION_SCHEMA.SCHEMATA view some of the column names changed.
Run these query's and you'll understand why he had to change the column from catalog_name to name..
SELECT * FROM INFORMATION_SCHEMA.SCHEMATA
SELECT * FROM sys.databases
In the info schema query the database name was stored in a column called CATALOG_NAME
In the sys.databases query the database name is stored in a column called NAME
If you change the underlying view and not the column name you will get errors saying invalid column name 'CATALOG_NAME'.
----------
Michael Abair
Database Administrator
Autotask Corporation
Autotask®
Makes your IT business run. Better.SM
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply