Common migration issues (version 2000 to 2005)

Many users complain that some new features are not working in SQL Server 2005 when the server is migrated from SQL Server 2000

These are the common tasks that should be done to avoid some migration issues

1 Change the compatibility level to 90
 
  EXEC sp_dbcmptlevel 'your_db_name',90

  This will solve the errors that occur during the execution of the code that contains newly introduced functions 
  like row_number(), rank(),etc

2 Enable Ad Hoc Distributed Queries

  EXEC SP_CONFIGURE 'show advanced options', 1
  RECONFIGURE
  go
  EXEC SP_CONFIGURE 'Ad Hoc Distributed Queries', 1
  RECONFIGURE
  go
  EXEC SP_CONFIGURE 'show advanced options', 0
  RECONFIGURE

  This will solve the errors that occur when you use OPENROWSET and OPENDATASOURCE commands

3 Install Service Pack2 for SQL Server 2005
 
  This will solve the errors that occur when you use Import/Export wizard

 

Published 12 August 2008 09:41 by Madhivanan
Filed under: , ,

Comments

# re: Common migration issues (version 2000 to 2005)

12 August 2008 17:29 by Scott R.

Madhivanan,

Good comments on the SQL 2000 - 2005 upgrade process.

One observation on item #3: You may want to update it to SQL 2005 SP2 (plus at least CU1 or later), as SQL 2005 SP1 was retired on April 8, 2008.  Refer to the Microsoft Lifecycle Supported Service Packs site (support.microsoft.com/.../lifesupsps) for further information.

Scott R.

# re: Common migration issues (version 2000 to 2005)

14 August 2008 14:33 by Madhivanan

Thanks Scott. I updated it

# Compatibility Level of a database

05 January 2009 15:01 by Madhivanan

When users migrate from SQL Server 2000 to SQL Server 2005, not all databases are set to Compatibility

# Compatibility Level of a database

05 January 2009 15:06 by SQL Server Transact-SQL (SSQA.net)

When users migrate from SQL Server 2000 to SQL Server 2005, not all databases are set to Compatibility