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 completeness of the source text.
  • Truncation or other corruption 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 upgrade_object returns an error. Create the temporary table exactly as expected by the compiled object, then execute dbcc upgrade_object again. Do not do this if the compiled object is upgraded automatically when it is invoked.
Reserved word errors If you load a database dump from an earlier version of SAP ASE into version 15.7 or later and the dump contains a stored procedure that uses a word that is now reserved, when you run dbcc upgrade_object on that stored procedure, the command returns an error. Either manually change the object name or use quotes around the object name, and issue the command set quoted identifiers on. Then drop and re-create the compiled object.