locked
Moving from SQL 2000 to 2005 RRS feed

  • Question

  • We recently copied database from SQL Server 2000 to 2005. Compatibility level is now "SQL Server 2000 (80)". We would like to switch compatibility to 2005.

    In this help article:

    http://msdn.microsoft.com/en-us/library/ms178653.aspx

    there is table called

    Differences Between Lower Compatibility Levels and Level 90


    Is there an automated tool that can go through the database and check for all issues mentioned in this table?
    Friday, July 11, 2008 9:37 PM

Answers

  • Joe,

     

    You will want to run the SQL Server Upgrade Advisor.  Generally speaking this would have been done prior to you moving the database to SQL 2005.

     

    Download details: Microsoft SQL Server 2005 Upgrade Advisor

     

     

    Saturday, July 12, 2008 8:29 PM
  • Compatibility mode helps sql server to interpret the T-SQL commands correctly. If you have use *= and =* operator in your procedure, the procedure will work fine until its in compatibility 80, if you change it to 90 then it won't work as this is not supported in compatibility 90. Hence before changing the compatibility make sure you have re-written your query. The KB article provides you the list of commands which are not supported in 90 compatibility level.

    Thursday, July 17, 2008 10:53 AM

All replies

  • Joe,

     

    You will want to run the SQL Server Upgrade Advisor.  Generally speaking this would have been done prior to you moving the database to SQL 2005.

     

    Download details: Microsoft SQL Server 2005 Upgrade Advisor

     

     

    Saturday, July 12, 2008 8:29 PM
  • Jonathan,

    I discovered SQL Server Upgrade Advisor after I posted. I ran it against our database on SQL Server 2000 and it didn't report any specific issues.

    Does SQL Server Upgrade advisor actually check for each and every issue from the table in help topic I mentioned in my original post? For instance the table says that
    "The *= and =* operators for outer join are supported with a warning message" in 2000 and not supported in 2005. Does it mean if I used these operators in my DB in 2000 Upgrade advisor would warn me?
    Monday, July 14, 2008 2:39 PM
  • Since i didn't program it, I can't actually tell you honestly what it is checking for exactly.  I know it scans your SQL code, and checks for incompatibilities, but it can't catch them if they don't exist in the database, so you can provide it a trace file to analyze.  Nothing is guaranteed though.  I personally have never had an issue with changing compatibility levels from 80 to 90 where the analyzer passed the database.  If there is a specific issue you are concerned about, you can check if it will be caught by adding a dummy procedure that exhibits the incompatible code, and rerun the analyzer.

    Monday, July 14, 2008 2:45 PM
  • I think SQL Server 2005 Upgrade Advisor could not help you to check all compatibilty issues you mentioned. In its help document, "compatibilty mode changes" page, you can see follow words:

     

    "When a database is upgraded to SQL Server 2005 from any earlier version of SQL Server, the database retains its existing compatibility level. The following compatibility-mode differences may affect your applications if you change the compatibility mode to 90 after upgrading".

     

     

    Thursday, July 17, 2008 8:05 AM
  • Compatibility mode helps sql server to interpret the T-SQL commands correctly. If you have use *= and =* operator in your procedure, the procedure will work fine until its in compatibility 80, if you change it to 90 then it won't work as this is not supported in compatibility 90. Hence before changing the compatibility make sure you have re-written your query. The KB article provides you the list of commands which are not supported in 90 compatibility level.

    Thursday, July 17, 2008 10:53 AM