Prepare the SQL Server 6.x Installation

It is essential that you verify the SQL Server 6.x installation before you begin the upgrade. The SQL Server 6.x (export) installation must meet the following requirements or the SQL Server Upgrade Wizard will not allow you to continue:

• The tempdb database must be at least 10 MB.

  The tempdb database must be at least 10 MB to support queries that SQL Server Upgrade Wizard runs against the SQL Server 6.x installation. If the tempdb database is not at least 10 MB, the upgrade will not continue. By default, the tempdb database in SQL Server 6.0 and SQL Server 6.5 is 2 MB; therefore, you may need to change the size of the database. Use exec sp_helpdb tempdb or SQL Server Enterprise Manager to verify the size of the tempdb database.

• The @@SERVERNAME must not be NULL.

  The SQL Server Upgrade Wizard uses the @@SERVERNAME function during the upgrade process. It is recommended that @@SERVERNAME be set to the same name as the computer name during the upgrade. If you run SELECT @@SERVERNAME and receive NULL, run exec sp_addserver ‘computername’, ‘local’ to reset the @@SERVERNAME function. For example, if the computer name is SQLPROD1, run exec sp_addserver ‘SQLPROD1’, ‘local’.

• The master database must have 3 MB of free space.

  When you run the SQL Server Upgrade Wizard, a Transact-SQL script is run against the export server to upgrade the system tables and stored procedures in the master database for SQL Distributed Management Objects (SQL-DMO) and other connectivity components. These changes are fully backward compatible, but allow later versions of Open Database Connectivity (ODBC) and SQL-DMO to use the SQL Server 6.x installation. To verify that you have 3 MB of free space, run the following script or use SQL Server Enterprise Manager:

  Use master

  Go

  exec sp_spaceused @updateusage = ‘TRUE’

    

  Verify that unallocated space is at least 3 MB (or 3072 KB).

• The text for objects must be intact in the syscomments system table.

  If you create a nontable object such as a trigger, view, or stored procedure, the text of the CREATE statement is stored in the syscomments system table. If a system administrator has deleted the text for an object, that object is not upgraded.

  The sp_rename system stored procedure does not change an object’s name in syscomments.

  If you use the sp_rename system stored procedure to rename an object, such as a view or stored procedure, the text of the original CREATE statement for that object is not modified. Therefore, when the object is migrated to SQL Server 7.0, it retains the name that it had before the rename.

• Logins must exist for every database user.

  If you have moved a database or restored a database to the SQL Server 6.x installation, you must verify that a login exists and is mapped properly for each user. You can do this manually in SQL Server 6.0 or use the sp_change_users_login stored procedure in SQL Server 6.5. If you run the stored procedure with no parameters in either database, it reports the users who do not have a correct login. You must correct login-to-user mappings before you begin an upgrade.

Note Stored procedures that modify system tables are not upgraded. Permissions that had no effect, such as EXECUTE on a table, are not upgraded.