Updated Writeup for ASE Error Message 225

One of my current projects is updating and expanding the ASE Troubleshooting and Error Messages Guide (TSG).  As I update existing error writeups and create new ones, I’ll be posting the new content to this blog rather than making you wait for the next edition of the TSG.  If there is an error message that you are particularly interested in seeing a writeup on, please feel free to send me a direct message giving the error number or text and any comments you may have on the message and I’ll plan to work on it sooner rather than later.  If your need for an explanation is urgent, please follow the usual process for opening a Technical Support case (SAP message) for assistance with your situation.

The following is an updated writeup for error 225 that adds information about inconsistent use of qualified object names causing the error in some (but not most) circumstances.

-bret

Error 225

Severity

11

Message text

Cannot run query–referenced object (name  %.*s) dropped during query optimization.

Explanation

This error occurs when Adaptive Server fails to build an execution plan for a stored procedure or an ad-hoc query because a referenced object was not found.

Known causes of this error include:

• The value of the number of open objects configuration parameter is too low
• Using a mix of qualified (database.owner.name or owner.name) and unqualified (name) object references.

There may be other causes.  This is not a serious error and does not indicate corruption. However, it may prevent you from running some stored procedures and ad-hoc queries.

Action

Using sp_monitorconfig “number of open objects”, check if the max_used value equals the current configuration value from sp_configure “number of open
objects”; if so increase the value if feasible. For information about memory-related configuration parameters, refer to:

• The chapter “Setting Configuration Parameters” in the System Administration Guide: Volume 1
• Adaptive Server Enterprise Performance and Tuning Guides

Check the code being executed for inconsistent use of qualified and unqualified object names and rewrite using consistently qualified names

Example

This code uses a mix of tempdb..t, dbo.t, and t to reference the same table:

       use tempdb

       go

create proc p as

  create table dbo.t(c1 int)

  insert t  select dbid from master..sysusages

  select * from tempdb..t where c1=1

go

The code can be rewritten using consistent naming as:

create proc p as

           create table dbo.t(c1 int)

  insert dbo.t  select dbid from master..sysusages

  select * from dbo.t where c1=1

   go

If neither action resolves the 225 errors, call SAP Product Support (Sybase Technical Support) for assistance.

Versions in which this error is raised

All versions