Cannot run query--referenced object dropped during query optimization

Hi,
while executing a procedure I am getting the following
error.
"Server Message: Number 225, Severity 16
Cannot run query--referenced object (name NAME NOT
RECOVERABLE) dropped during query optimization. "

Sybase version is -Adaptive Server Enterprise/12.5.3/EBF
12150

any idea?

Thanks,
Reliance

Reliance wrote:
> Hi,
> while executing a procedure I am getting the following
> error.
> "Server Message: Number 225, Severity 16
> Cannot run query--referenced object (name NAME NOT
> RECOVERABLE) dropped during query optimization. "
>
> Sybase version is -Adaptive Server Enterprise/12.5.3/EBF
> 12150
>
> any idea?
>
> Thanks,
> Reliance

Error 225 can be caused by inconsistent naming such as a mix of
owner.object vs object references.

As an example

use tempdb
go
create proc p_qual as
create table dbo.tabq(c1 int)
insert tabq select dbid from master..sysusages
select * from tabq where c1=1
go

Should be rewritten as

create proc p_qual as
create table dbo.tabq(c1 int)
insert dbo.tabq select dbid from master..sysusages
select * from dbo.tabq where c1=1
go

Bret,
I have encountered the same type problem, when I created the
tables and refering to them in teh same way:
use db1
go
create table tempdb..aa ( a int)
go
insert into tempdb..aa values(1)

Now when I do the following, I have the mesage 225:
use db1
go
select * from tempdb..aa


Can this be explained in terms of what you mention, I do not
see inconsistency in the naming of the table and the way
that I access them, is there one?



> Reliance wrote:
> > Hi,
> > while executing a procedure I am getting the following
> > error.
> > "Server Message: Number 225, Severity 16
> > Cannot run query--referenced object (name NAME NOT
> > RECOVERABLE) dropped during query optimization. "
> >
> > Sybase version is -Adaptive Server Enterprise/12.5.3/EBF
> > 12150
> >
> > any idea?
> >
> > Thanks,
> > Reliance
>
> Error 225 can be caused by inconsistent naming such as a
> mix of owner.object vs object references.
>
> As an example
>
> use tempdb
> go
> create proc p_qual as
> create table dbo.tabq(c1 int)
> insert tabq select dbid from master..sysusages
> select * from tabq where c1=1
> go
>
> Should be rewritten as
>
> create proc p_qual as
> create table dbo.tabq(c1 int)
> insert dbo.tabq select dbid from master..sysusages
> select * from dbo.tabq where c1=1
> go

> On 2008-08-28 06:32:37 +1000, tartampion said:

We do:
• create "permanent" tempdb tables in tempdb via script upon boot
•Â*populate some via DML in tempdb
•Â*populate others via stored procs (which means whenever the user runs
them; from their user_db)
•Â*interrogate these via stored procs (which means whenever the user
runs them; from their user_db; millions of times a day)
•Â*occasionally interrogate these via isql/DBArtisan
All references are "tempdb..mytable".

It is definitely a bug.

But additionally, that it happens for you and not for us (others ?)
means there is something else that is different; if you determine that,
you might be able to circumvent the buggy code. The only diff I see is
you are doing create table from your Database; we are doing it in tempdb.
Note the sprocs are not rebuild; the tables are recreated everytime on
boot. If and when a sproc is rebuilt, the create table is outside and
the table exists.

I would
--
Cheers
Derek
Senior Sybase DBA / Information Architect
Copyright © 2008 Software Gems Pty Ltd
Quality Standards = Zero Maintenance + Zero Surprises
Performance Standards = Predictability + Scaleability