Upgrade from sql 2000 to 2005

• klnsuddu

  SSCertifiable

  Points: 5836

  More actions

  March 25, 2009 at 1:30 am

  #132501

  hi,

  We are upgrading sql 2000 to 2005.Below are the issue detected by Upgrade Adviser:

  1.Upgrade Advisor detected statements that reference the INFORMATION_SCHEMA.SCHEMATA view.

  In earlier versions of SQL Server, this view returned all databases in an instance of SQL Server.

  In SQL Server 2005, the view returns all schemas in a database

  Effected objects:

  Source Type: Database

  Database: ABC

  Object Name: SP_Utility

  Object Type: P

  Source Type: Database

  Database: ABC

  Object Name: SP_Utility_DB

  Object Type: P

  Corrective Action: Modify your application to reference the sys.databases catalog view to return all databases in an instance of SQL Server

  My Question is 'Changing INFORMATION_SCHEMA.SCHEMATA to sys.databases' is required when we change the Compatibility level to 90 right?

  We want to keep the compatibility level as 80, in this NO NEED to change INFORMATION_SCHEMA.SCHEMATA to sys.databases right?

  please clarify me

  thanks

• Eswin

  SSCrazy Eights

  Points: 8320

  More actions

  March 25, 2009 at 2:41 am

  #965840

  Yes if are going to keep the compatibility level as 80 you don't have to change

  Tanx

• Greg Charles

  SSC-Forever

  Points: 45403

  More actions

  March 25, 2009 at 10:46 am

  #966117

  Actually, if you're using the view to get a list of databases, you WILL need to change the stored procedures. The compatibility level of your databases makes no difference. Read the description of INFORMATION_SCHEMA.SCHEMATA in SQL 2005 BooksOnLine. It clearly says that the changes break backward compatibility.

  Greg