Filter
Top Products
Upgrading compiled objects with dbcc upgrade_object
112 Adaptive Server Enterprise
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.