Adaptive Server version 11.9.3 introduced the process of upgrading compiled objects based on their source text. Compiled objects are:
Check constraints
Defaults
Rules
Stored procedures (including extended stored procedures)
Triggers
Views
The source text of each compiled object is stored in the syscomments table, unless it has been manually deleted. When you upgrade the server, the existence of the source text in syscomments is verified during that process. However, the compiled objects are not actually upgraded until they are invoked.
For example, if you have a user-defined stored procedure named list_proc, the presence of source text for list_proc is verified when you upgrade to Adaptive Server 12.5.x. The first time list_proc is invoked after the upgrade, Adaptive Server detects that the list_proc compiled object has not been upgraded. Adaptive Server recompiles list_proc, based on the source text in syscomments. The newly compiled object is then executed.
Upgraded objects retain the same object ID and permissions that they used before being upgraded.
Compiled objects for which the source text was hidden using sp_hidetext are upgraded in the same manner as objects for which the source text is not hidden. For information on sp_hidetext, see the Reference Manual: Procedures.
If you are upgrading from 32-bit installations to use
a 64-bit Adaptive Server, the size of each 64-bit compiled object
in the sysprocedures table in each database
increases by approximately 55 percent when the object is upgraded.
The pre-upgrade process calculates the exact size. Increase
your upgraded database size accordingly.
To ensure that compiled objects have been upgraded successfully before they are invoked, you can upgrade them manually using the dbcc upgrade_object command. For details, see “Finding compiled object errors before production”.
