Sybase Adaptive Server 15.0.2, Installation Manual

Page: 126 / 160

Upgrading compiled objects with dbcc upgrade_object
112
Adaptive Server Enterprise
If quoted identifier errors occur, use the
set
command to activate
quoted
identifiers
, and then run
dbcc upgrade_object
to upgrade the object.
For compiled objects created in version 11.9.2 or later, the upgrade process
automatically activates or deactivates quoted identifiers as appropriate.
Note Quoted identifiers are not the same as literals enclosed in double quotes.
The latter do not require you to perform any special action before the upgrade.
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. To correct this error,
create the temporary table exactly as expected by the compiled object, then
execute
dbcc upgrade_object
again. You need not do this if the compiled object
is upgraded automatically when it is invoked.
select * potential problem areas
In Adaptive Server version 11.9.3 and later, the results of a
select *
clause in a
stored procedure, trigger, or view that was created in an earlier version of
Adaptive Server may be different from what you expect.
For more information about the changes, see the Reference Manual.
If
dbcc upgrade_object
finds a
select *
clause in the outermost query block of a
stored procedure, it returns an error, and does not upgrade the object.
For example, consider the following stored procedures:
create procedure myproc as
select * from employees
go
create procedure yourproc as
if exists (select * from employees)
print "Found one!"
go
dbcc upgrade_object
returns an error on
myproc
because
myproc
includes a
statement with a
select *
clause in the outermost query block. This procedure is
not upgraded.
dbcc upgrade_object
does not return an error on
yourproc
because the
select *
clause occurs in a subquery. This procedure is upgraded.