Home

	Expand Search

Installation Guide Adaptive Server Enterprise for IBM RISC System/6000 AIX

Chapter 8 Upgrading Sybase Servers

Pre-upgrade tasks

Reserved words

Reserved words

Reserved words are pieces of SQL syntax that have special meaning when used as part of a command.

Transact-SQL does not allow words that are part of command syntax to be used as identifiers, unless they are enclosed in quotation marks, as explained in "Using quoted identifiers". If you are upgrading Adaptive Server, and the identifiers in your user databases match new reserved words, errors can result when you run queries, stored procedures, or applications that use these identifiers.

Warning!

If a user database name is a reserved word, upgrade fails when it tries to upgrade the database. You must use sp_renamedb to change the name of any user database that is a reserved word before performing the upgrade.

Conflicts between object names do not prevent the upgrade process from completing. However, applications that refer to conflicting object names may not work after the upgrade. Rename all objects that use reserved words.

As part of the pre-upgrade process, sqlupgrade can perform the reserved word check for you. See the Reference Manual for a complete list of reserved words.

If you change an object name, change applications and stored procedures that refer to that object.

If you plan to use sqlupgrade to perform the upgrade process, you do not have to check for reserved words as a separate step. In sqlupgrade, you can check for reserved words at the start of the upgrade process, and then continue with the upgrade if no problems are found.

If you plan to use sqlupgraderes to perform the upgrade process using a resource file, you can run sqlupgrade first to identify reserved word conflicts and other potential upgrade eligibility problems. You can specify that you do not want the upgrade to proceed even if no problems are found.

If you use either sqlupgrade or sqlupgraderes, they automatically install new reserved words and sp_checkreswords, a stored procedure used to detect and display any identifiers in your existing databases that conflict with reserved words in the new database. You can then run the new version of sp_checkreswords at anytime while preforming pre-upgrade tasks.

You must use the sp_checkreswords stored procedure from the new version of Adaptive Server to verify that the old installation of Adaptive Server does not use any reserved words that were introduced with the new server.

Checking for reserved words generates a list of identifiers that conflict with reserved words, and the owners of those identifiers, in the file $SYBASE/$SYBASE_ASE/init/logs/sqlupgradeMMDD.nnn. Review this file to determine which identifiers must be changed.

If any database names are reserved words, you must use sp_renamedb to change the database names before you can upgrade. Use sp_dboption to set the database to single-user mode, and then run sp_renamedb, specifying the new name. See the Reference Manual for more information on these procedures.

If other identifiers are reserved words, you can:

• Use sp_rename to change the name of the object, before or after the upgrade.

• Use quoted identifiers. See "Using quoted identifiers" for more information.

Run sp_checkreswords in master and in each user database to display the names and locations of conflicting identifiers.

For more information about sp_rename and sp_checkreswords and methods for avoiding reserved word conflicts, see the Reference Manual.

You can enclose the identifiers that are reserved words in double quotation marks and invoke the quoted_identifier option of the set command in procedures and queries that include the reserved words. The set quoted_identifier option tells Adaptive Server to treat any character string enclosed in double quotation marks as an identifier.

To avoid reserved word conflicts, all users on the server must invoke the quoted_identifier option in all stored procedures and queries that include the reserved words.