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, say 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. Then, 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 prior to being upgraded.
Compiled objects for which the source text was hidden using sp_hidetext are upgraded like objects for which the source text is not hidden. For information on sp_hidetext, see the Reference Manual.
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 calculates the exact
size. Be sure to 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.”
