Skip to Content

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

To report this post you need to login first.

1 Comment

You must be Logged on to comment or reply to a post.

  1. Sean Venter

    Hi Bret,

    We have also recently received the Error 225, after running the same stored procedure for a while for no apparent reason the server started to raise the error.

    After scrounging around on the web for possible solutions we found that a mix of owner.object could also cause the error. We in fact did find that the view was made of a mixture of just calling the underlying object and owner.object.

    By dropping and re-creating the view with owner.object was the fix for us.


    Interestingly..

    In this environment we also use statement cache. As a test, prior to changing the view to reference all objects as owner.object, I ran the same select from the view with the statement cache disabled, in the session only, and this worked.

    What I could deduce from this was that the query plan in the statement cache for that query could not find/reference the object(s) it was looking for.


    Number of open objects where at a very low usage.


    (0) 

Leave a Reply