INF: How to Use Check65.exe to Check for Problems Before You Upgrade to SQL Server 7.0 (255254)

SUMMARY

The supported method to upgrade databases from Microsoft SQL Server 6.5 to Microsoft SQL Server 7.0 is to run the Upgrade Wizard that is installed with SQL Server 7.0.

The Upgrade Wizard displays a text file before you click the Finish button in the wizard to start the actual upgrade process, which warns you of potential problems with your SQL Server 6.5 databases. To prepare for an upgrade of a production SQL Server 6.5 server you can run the Upgrade Wizard until you see the message "Click Finish to begin your upgrade" to collect the list of warnings.

Because the Upgrade Wizard actually calls a command line utility to generate this text file, you can also obtain the list of warnings without running the graphical Upgrade Wizard.

MORE INFORMATION

A utility, Check65.exe, is installed to the \Mssql7\Upgrade directory. Check65.exe performs the following checks to detect common problems on a SQL Server 6.5 server before an upgrade:

All CREATE PROCEDURE, CREATE VIEW, and CREATE TRIGGER statements are run with NOEXEC ON, ANSI_NULLS OFF, and QUOTED_IDENTIFIER ON. A failure of any one of these CREATE statements due to syntax errors, missing dependent objects, missing syscomments text or other problems is logged.
Detects renamed stored procedures, views, and triggers. If one of these objects is renamed by using the sp_rename stored procedure, the name of the object in the CREATE script stored in the syscomments system table is not updated. Because these objects are re-created in SQL Server 7.0 using the text that is stored in the SQL Server 6.5 database, this condition creates a problem for the Upgrade Wizard. For additional information, click the article number below to view the article in the Microsoft Knowledge Base:
243198 INF: Renaming SP, View or Trigger Does Not Update SYSCOMMENTS

Before you start the upgrade, you should address all problems on the SQL Server 6.5 server reported by Check65.exe.

IMPORTANT: The purpose of Check65.exe is to detect some of the most common causes of problems during upgrades from SQL Server 6.5. Having a clean Check65.exe report does not guarantee that you will not encounter any problems during the upgrade. It only indicates that the two checks described previously did not detect any issues. You should perform an actual upgrade in a test environment and run DBCCs on the SQL Server 6.5 databases to identify any remaining problems.

The command line syntax for Check65.exe is:

  CHECK65 <StdOutFile> <StdErrFile> <DSN> <Login> <Password> <DBName1> <DBName2> ...

    <StdOutFile>  Informational messages and ODBC errors (connection 
                  errors, database name not found, and so forth).

    <StdErrFile>  Report generated for problems that will affect an 
                  upgrade (renamed objects, and so forth).

    <DSN>         Name of an ODBC Data Source Name (DSN) for the 
                  SQL Server 6.5 server.

    <Login>       Must be system administrator (sa) because CHECK65 must 
                  run SETUSER to impersonate each object owner's security 
                  context.

    <Password>    The sa password. Use "" if the password is NULL.

    <DBNameN>     Lists the databases to check, separated by spaces.

All the parameters and at least one database name are required. Parameters that include spaces must be surrounded by double quotes. If the StdOutFile or StdErrFile parameter is an empty string ("") the output is sent to stderr or stdout streams.

Sample command lines:

C:\MSSQL7\Upgrade>check65 "" "" SQL65SRV sa "" pubs mydatabase C:\MSSQL7\Upgrade>check65 c:\65chk.err c:\65chk.out SQL65SRV sa pwd pubs

Autotranslation (the Perform translation for character data check box in the DSN Configuration Wizard dialog box) should be turned off for the ODBC data source.

The SQL Server 6.5 databases must have the read only database option set to False. Read only databases are skipped and the following error message is logged:

Database <dbname> has the Read Only Option set and will not be checked.

Check65.exe exits with a return code of zero if Check65.exe runs successfully and a return code of one if errors are encountered. Detecting problems with SQL Server 6.5 objects does mean that the execution was unsuccessful. For example, if Check65.exe finds that syscomments text is missing for an object, it may still exit with a result code of zero (success).