Installation guide

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_checksource and sp_hidetext, see Reference Manual: Procedures .
Note: If you are upgrading from a 32-bit to 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 preupgrade process calculates the exact size;
increase your upgraded database size accordingly.
To determine whether a compiled object has been upgraded, and you are upgrading to a 64-bit
pointer size in the same version, look at the sysprocedures.status column. It contains a
hexadecimal bit setting of 0x2 to indicate that the object uses 64-bit pointers. If this bit is not
set, the object is a 32-bit object, which means the object has not been upgraded.
To ensure that compiled objects have been upgraded successfully before they are invoked,
upgrade them manually using the dbcc upgrade_object command.
Finding Compiled Object Errors Before Production
Use dbcc upgrade_object to identify potential problem areas that may require manual
changes to achieve the correct behavior.
After reviewing the errors and potential problem areas, and fixing those that need to be
changed, use dbcc upgrade_object to upgrade compiled objects manually instead of waiting
for the server to upgrade the objects automatically.
Problem Description Solution
Missing, truncated,
or corrupted source
text
If the source text in syscomments has been
deleted, truncated, or otherwise corrupted,
dbcc upgrade_object may report syntax errors.
If:
• The source text was not
hidden – use sp_helptext
to verify the complete-
ness of the source text.
• Truncation or other cor-
ruption has occurred –
drop and re-create the
compiled object.
Temporary table
references
If a compiled object, such as a stored procedure
or trigger refers to a temporary table (#temp
table_name ) that was created outside the body
of the object, the upgrade fails, and dbcc up-
grade_object returns an error.
Create the temporary table
exactly as expected by the
compiled object, then exe-
cute dbcc upgrade_object
again. Do not do this if the
compiled object is upgraded
automatically when it is in-
voked.
CHAPTER 10: Adaptive Server Upgrades
102 Adaptive Server Enterprise