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