Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Object cannot be found error

4 posts in General Discussion Last posting was on 2012-06-18 16:17:30.0Z
dougaug Posted on 2012-05-18 00:34:19.0Z
Sender: 72f7.4fb595c5.1804289383@sybase.com
From: dougaug
Newsgroups: sybase.public.ase.general
Subject: Object cannot be found error
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4fb5990b.736a.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 17 May 2012 17:34:19 -0700
X-Trace: forums-1-dub 1337301259 172.20.134.41 (17 May 2012 17:34:19 -0700)
X-Original-Trace: 17 May 2012 17:34:19 -0700, 172.20.134.41
Lines: 110
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31119
Article PK: 74012

I got a strange error running a Sybase stored procedure
these days.

I will try explain the context and wish some comments and
explanations.

I also would like to advise that some aspects about the
situation couldn't be changed, because I'm in an environment
where I have limited privileges (I can only change the
stored procedure, nothing else).

1 - I'm using Sybase 12.5 (Adaptive Server
Enterprise/12.5.4/EBF 15434 ESD#8/P/RS6000/AIX
5.1/ase1254/2105/64-bit/FBO/Sat Mar 22 19:39:46 2008)

2 - I wrote a full stored procedure which simplified version
follows below (It's a simplified version but could be used
to simulate the error):

-- The procedure must be created in db1
use db1
go

if object_id('tempdb..tb1') is not null
drop table tempdb..tb1
go

drop proc stproc1
go

create proc stproc1 as
begin
create table tempdb..tb1
(c1 int)

if @@error = 0
begin
insert into tempdb..tb1 select 11
insert into tempdb..tb1 select 21
return 1 -- This just exits the procedure as if "there
was an error"
end

select top 1 * from tempdb..tb1
delete top 1 from tempdb..tb1
select top 1 * from tempdb..tb1
delete top 1 from tempdb..tb1

drop table tempdb..tb1
end
go

3 - The procedure is compiled in production environment
using a login called user1 (through a in-house tool).
This login is aliased to dbo on databases tempdb and db1 (I
can't change the user permissions nor the user).

4 - To run the stored procedure, I'm using a login called
user2 (this couldn't be changed).
This login is aliased to dbo on database db1 and is guest on
tempbdb.

5 - In my environment there is a very high probability to
occur a deadlock (it's a very intensive OLTP system).
When I ran my procedure in production environment, a
deadlock occurred. When I reran that, I got this error:


Msg 267, Level 16, State 2
Object 'tempdb..tb1' cannot be found.


What has made me confused is that the table was still there
(I opened another session on database and queried the table)
and when we don't reference the user in object name it
assumes the current user (in the case, login user2 points to
user guest on tempdb).

6 - To correct this error, I changed the stored procedure
replacing all tempdb..tb1 occurrences by tempdb.guest.tb1.
With this modification, when I ran the procedure first time
again another deadlock occured. When I reran it (cause of a
deadlock), it ended OK as I've expected.

7 - To simulate this situation, I wrote the simplified
procedure above.
When I ran the simplified procedured first time (using the
login user2), it exited with return status 1 (there is an
explicit return inside it, to simulate that an error
occurred and it exits gracefully).
When I reran the procedure above again, it ended OK (very
strange...).

8 - To simulate an "unhandled" error in an development
environment, I just replaced "return 1" by "waitfor delay
00:00:10". After, I compiled the procedure using the login
"user1" and ran it again with login "user2". While it was
waiting for 10 seconds, I stopped its execution (I'm using
SQL Advantage).
When I restarted it again, got the error 267 as occurred in
my production environment.

I wish someone could explain why this behavior is happening,
if it has some relationship with Sybase procedure
compilation proccess, user permissions or if it's a Sybase
bug.

Thanks,

Douglas


Bret Halford Posted on 2012-05-18 16:53:55.0Z
From: Bret Halford <bret@sybase.com>
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:12.0) Gecko/20120428 Thunderbird/12.0.1
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Object cannot be found error
References: <4fb5990b.736a.1681692777@sybase.com>
In-Reply-To: <4fb5990b.736a.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4fb67ea3$1@forums-1-dub>
Date: 18 May 2012 09:53:55 -0700
X-Trace: forums-1-dub 1337360035 10.22.241.152 (18 May 2012 09:53:55 -0700)
X-Original-Trace: 18 May 2012 09:53:55 -0700, vip152.sybase.com
Lines: 158
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31126
Article PK: 74017


On 5/17/2012 6:34 PM, dougaug wrote:
> I got a strange error running a Sybase stored procedure
> these days.
>
> I will try explain the context and wish some comments and
> explanations.
>
> I also would like to advise that some aspects about the
> situation couldn't be changed, because I'm in an environment
> where I have limited privileges (I can only change the
> stored procedure, nothing else).
>
> 1 - I'm using Sybase 12.5 (Adaptive Server
> Enterprise/12.5.4/EBF 15434 ESD#8/P/RS6000/AIX
> 5.1/ase1254/2105/64-bit/FBO/Sat Mar 22 19:39:46 2008)
>
> 2 - I wrote a full stored procedure which simplified version
> follows below (It's a simplified version but could be used
> to simulate the error):
>
> -- The procedure must be created in db1
> use db1
> go
>
> if object_id('tempdb..tb1') is not null
> drop table tempdb..tb1
> go
>
> drop proc stproc1
> go
>
> create proc stproc1 as
> begin
> create table tempdb..tb1
> (c1 int)
>
> if @@error = 0
> begin
> insert into tempdb..tb1 select 11
> insert into tempdb..tb1 select 21
> return 1 -- This just exits the procedure as if "there
> was an error"
> end
>
> select top 1 * from tempdb..tb1
> delete top 1 from tempdb..tb1
> select top 1 * from tempdb..tb1
> delete top 1 from tempdb..tb1
>
> drop table tempdb..tb1
> end
> go
>
> 3 - The procedure is compiled in production environment
> using a login called user1 (through a in-house tool).
> This login is aliased to dbo on databases tempdb and db1 (I
> can't change the user permissions nor the user).
>
> 4 - To run the stored procedure, I'm using a login called
> user2 (this couldn't be changed).
> This login is aliased to dbo on database db1 and is guest on
> tempbdb.
>
> 5 - In my environment there is a very high probability to
> occur a deadlock (it's a very intensive OLTP system).
> When I ran my procedure in production environment, a
> deadlock occurred. When I reran that, I got this error:
>
>
> Msg 267, Level 16, State 2
> Object 'tempdb..tb1' cannot be found.
>
>
> What has made me confused is that the table was still there
> (I opened another session on database and queried the table)
> and when we don't reference the user in object name it
> assumes the current user (in the case, login user2 points to
> user guest on tempdb).
>
> 6 - To correct this error, I changed the stored procedure
> replacing all tempdb..tb1 occurrences by tempdb.guest.tb1.
> With this modification, when I ran the procedure first time
> again another deadlock occured. When I reran it (cause of a
> deadlock), it ended OK as I've expected.
>
> 7 - To simulate this situation, I wrote the simplified
> procedure above.
> When I ran the simplified procedured first time (using the
> login user2), it exited with return status 1 (there is an
> explicit return inside it, to simulate that an error
> occurred and it exits gracefully).
> When I reran the procedure above again, it ended OK (very
> strange...).
>
> 8 - To simulate an "unhandled" error in an development
> environment, I just replaced "return 1" by "waitfor delay
> 00:00:10". After, I compiled the procedure using the login
> "user1" and ran it again with login "user2". While it was
> waiting for 10 seconds, I stopped its execution (I'm using
> SQL Advantage).
> When I restarted it again, got the error 267 as occurred in
> my production environment.
>
> I wish someone could explain why this behavior is happening,
> if it has some relationship with Sybase procedure
> compilation proccess, user permissions or if it's a Sybase
> bug.
>
> Thanks,
>
> Douglas

The issue has to do with ASE's object resolution
process. A similar issue was reported under CR 473988
(though this involved a subprocedure that dropped and
recreated the table). The CR was closed as "expected behavior',
noting that ASE generally resolves table references as
the procedure creator (dbo in your case), but "create table"
resolves as the executor of the procedure. If something
happens that requires renormalization of the procedure,
ASE first tries to access objects by the object id in the
plan, then by name, but does so as owner "tempdb.dbo.tb1"
rather than "tempdb.guest.tb1".

Fully qualifying the name in the procedure code is a very
valid approach (i.e. always using tempdb.guest.tb1 or
always using tempdb.dbo.tb1).

However, would be better still to use a hash temporary
table. Using a hash temp table will avoid the problem
of collisions if there is other code (or multiple sessions
running this proc at the same time) dropping and creating
objects using the same "tb1" name.

ASE will automatically drop and clean up the hash temp
table as the procedure context is exited.

create proc stproc1 as
begin
create table #tb1
(c1 int)

if @@error = 0
begin
insert into #tb1 select 11
insert into #tb1 select 21
return 1 -- This just exits the procedure as if "there was an error"
end

select top 1 * from #tb1
delete top 1 from #tb1
select top 1 * from #tb1
delete top 1 from #tb1

drop table #tb1
end
go


Douglas Cezari Posted on 2012-05-22 23:19:02.0Z
Sender: 1b5e.4fbc155e.1804289383@sybase.com
From: Douglas Cezari
Newsgroups: sybase.public.ase.general
Subject: Re: Object cannot be found error
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4fbc1ee6.1c73.1681692777@sybase.com>
References: <4fb67ea3$1@forums-1-dub>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 22 May 2012 16:19:02 -0700
X-Trace: forums-1-dub 1337728742 172.20.134.41 (22 May 2012 16:19:02 -0700)
X-Original-Trace: 22 May 2012 16:19:02 -0700, 172.20.134.41
Lines: 211
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31131
Article PK: 74023


> On 5/17/2012 6:34 PM, dougaug wrote:
> > I got a strange error running a Sybase stored procedure
> > these days.
> >
> > I will try explain the context and wish some comments
> > and explanations.
> >
> > I also would like to advise that some aspects about the
> > situation couldn't be changed, because I'm in an
> > environment where I have limited privileges (I can only
> > change the stored procedure, nothing else).
> >
> > 1 - I'm using Sybase 12.5 (Adaptive Server
> > Enterprise/12.5.4/EBF 15434 ESD#8/P/RS6000/AIX
> > 5.1/ase1254/2105/64-bit/FBO/Sat Mar 22 19:39:46 2008)
> >
> > 2 - I wrote a full stored procedure which simplified
> > version follows below (It's a simplified version but
> > could be used to simulate the error):
> >
> > -- The procedure must be created in db1
> > use db1
> > go
> >
> > if object_id('tempdb..tb1') is not null
> > drop table tempdb..tb1
> > go
> >
> > drop proc stproc1
> > go
> >
> > create proc stproc1 as
> > begin
> > create table tempdb..tb1
> > (c1 int)
> >
> > if @@error = 0
> > begin
> > insert into tempdb..tb1 select 11
> > insert into tempdb..tb1 select 21
> > return 1 -- This just exits the procedure as if
> > "there was an error"
> > end
> >
> > select top 1 * from tempdb..tb1
> > delete top 1 from tempdb..tb1
> > select top 1 * from tempdb..tb1
> > delete top 1 from tempdb..tb1
> >
> > drop table tempdb..tb1
> > end
> > go
> >
> > 3 - The procedure is compiled in production environment
> > using a login called user1 (through a in-house tool).
> > This login is aliased to dbo on databases tempdb and db1
> > (I can't change the user permissions nor the user).
> >
> > 4 - To run the stored procedure, I'm using a login
> > called user2 (this couldn't be changed).
> > This login is aliased to dbo on database db1 and is
> > guest on tempbdb.
> >
> > 5 - In my environment there is a very high probability
> > to occur a deadlock (it's a very intensive OLTP system).
> > When I ran my procedure in production environment, a
> > deadlock occurred. When I reran that, I got this error:
> >
> >
> > Msg 267, Level 16, State 2
> > Object 'tempdb..tb1' cannot be found.
> >
> >
> > What has made me confused is that the table was still
> > there (I opened another session on database and queried
> > the table) and when we don't reference the user in
> > object name it assumes the current user (in the case,
> > login user2 points to user guest on tempdb).
> >
> > 6 - To correct this error, I changed the stored
> > procedure replacing all tempdb..tb1 occurrences by
> > tempdb.guest.tb1. With this modification, when I ran the
> > procedure first time again another deadlock occured.
> > When I reran it (cause of a deadlock), it ended OK as
> I've expected. >
> > 7 - To simulate this situation, I wrote the simplified
> > procedure above.
> > When I ran the simplified procedured first time (using
> > the login user2), it exited with return status 1 (there
> > is an explicit return inside it, to simulate that an
> > error occurred and it exits gracefully).
> > When I reran the procedure above again, it ended OK
> > (very strange...).
> >
> > 8 - To simulate an "unhandled" error in an development
> > environment, I just replaced "return 1" by "waitfor
> > delay 00:00:10". After, I compiled the procedure using
> > the login "user1" and ran it again with login "user2".
> > While it was waiting for 10 seconds, I stopped its
> > execution (I'm using SQL Advantage).
> > When I restarted it again, got the error 267 as occurred
> > in my production environment.
> >
> > I wish someone could explain why this behavior is
> > happening, if it has some relationship with Sybase
> > procedure compilation proccess, user permissions or if
> > it's a Sybase bug.
> >
> > Thanks,
> >
> > Douglas
>
>
> The issue has to do with ASE's object resolution
> process. A similar issue was reported under CR 473988
> (though this involved a subprocedure that dropped and
> recreated the table). The CR was closed as "expected
> behavior', noting that ASE generally resolves table
> references as the procedure creator (dbo in your case),
> but "create table" resolves as the executor of the
> procedure. If something happens that requires
> renormalization of the procedure, ASE first tries to
> access objects by the object id in the plan, then by name,
> but does so as owner "tempdb.dbo.tb1" rather than
> "tempdb.guest.tb1".
>
> Fully qualifying the name in the procedure code is a very
> valid approach (i.e. always using tempdb.guest.tb1 or
> always using tempdb.dbo.tb1).
>
> However, would be better still to use a hash temporary
> table. Using a hash temp table will avoid the problem
> of collisions if there is other code (or multiple sessions
> running this proc at the same time) dropping and creating
> objects using the same "tb1" name.
>
> ASE will automatically drop and clean up the hash temp
> table as the procedure context is exited.
>
> create proc stproc1 as
> begin
> create table #tb1
> (c1 int)
>
> if @@error = 0
> begin
> insert into #tb1 select 11
> insert into #tb1 select 21
> return 1 -- This just exits the procedure as if
> "there was an error"
> end
>
> select top 1 * from #tb1
> delete top 1 from #tb1
> select top 1 * from #tb1
> delete top 1 from #tb1
>
> drop table #tb1
> end
> go

Thanks a lot for your reply.

Let's enumerate some points:

1 - A hash temp table doesn't solve my problem because since
I "expect" my process will stop (because of a dealock) I
need the data persist among every stored procedure
executions (in my production stored procedure, the process
of select and insert data in t1 table will take a lot of
time and I'am using this table to avoid this work in every
reexecution).
2 - See if my understanding about your explanation and
Sybase compilation is correct:
a) during normalization process, when ASE sees a "create
table" statement in a stored procedure, it doesn't resolve
the table name immediately (assuming it's not fully
qualified as create table tempdb..tb1). Then it creates a
temporary table to aid in parse time and then drops it
automatically at the end (I think in parse tree anyway there
is an indication that this object should be resolved in
runtime).
b) other references to tempdb..tb1 are resolved using the
object id from the "parse time created temporary table". In
the case above, since the login creating the stored
procedure is dbo in tempdb (login user1), the object is
implicitly qualified and resolved as "tempdb.dbo.tb1".
c) during execution, the create table statement creates a
table qualified as "tempdb.guest.tb1" (the login user2
running it is guest in tempdb) and it forces a reresolution
of entire stored procedure (all others references to
tempdb..tb1 are now resolved as tempdb.guest.tb1) and it
works fine. At end, if there is no errors, tempdb.guest.tb1
is dropped, otherwise it remains waiting the following
executions. If I'll reexecute the procedure it should be
resolved again according the current execution user (and
depending the return status from previous execution, table
tempdb.guest.tb1 still exists).

If my understanding is correct, I still can't see why this
error just occurs if the preceding execution ended in an
unhandled way (as I've stated above, when it first ends
without errors or with a return due a handled error, the
following executions doesn't show the error 267).

P.S.: Even dropping and recreating the stored procedure like
above after error 267 (without dropping tempdb.guest.tb1)
solves the problem (I must drop tempdb.guest.tb1 to
reexecute stored procedure without any errors).

Greetings and waiting for your comments.


Manish Negandhi [TeamSybase] Posted on 2012-06-18 16:17:30.0Z
From: "Manish Negandhi [TeamSybase]" <nospam_negandhi.manish@gmail.com>
Newsgroups: sybase.public.ase.general
References: <4fb67ea3$1@forums-1-dub> <4fbc1ee6.1c73.1681692777@sybase.com>
Subject: Re: Object cannot be found error
Lines: 135
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3664
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3664
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4fdf549a$1@forums-1-dub>
Date: 18 Jun 2012 09:17:30 -0700
X-Trace: forums-1-dub 1340036250 10.22.241.152 (18 Jun 2012 09:17:30 -0700)
X-Original-Trace: 18 Jun 2012 09:17:30 -0700, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31194
Article PK: 74082


>> The issue has to do with ASE's object resolution
>> process. A similar issue was reported under CR 473988
>> (though this involved a subprocedure that dropped and
>> recreated the table). The CR was closed as "expected
>> behavior', noting that ASE generally resolves table
>> references as the procedure creator (dbo in your case),
>> but "create table" resolves as the executor of the
>> procedure. If something happens that requires
>> renormalization of the procedure, ASE first tries to
>> access objects by the object id in the plan, then by name,
>> but does so as owner "tempdb.dbo.tb1" rather than
>> "tempdb.guest.tb1".
>>
>> Fully qualifying the name in the procedure code is a very
>> valid approach (i.e. always using tempdb.guest.tb1 or
>> always using tempdb.dbo.tb1).
>>
>> However, would be better still to use a hash temporary
>> table. Using a hash temp table will avoid the problem
>> of collisions if there is other code (or multiple sessions
>> running this proc at the same time) dropping and creating
>> objects using the same "tb1" name.
>>
>> ASE will automatically drop and clean up the hash temp
>> table as the procedure context is exited.
>>
>> create proc stproc1 as
>> begin
>> create table #tb1
>> (c1 int)
>>
>> if @@error = 0
>> begin
>> insert into #tb1 select 11
>> insert into #tb1 select 21
>> return 1 -- This just exits the procedure as if
>> "there was an error"
>> end
>>
>> select top 1 * from #tb1
>> delete top 1 from #tb1
>> select top 1 * from #tb1
>> delete top 1 from #tb1
>>
>> drop table #tb1
>> end
>> go
>
> Thanks a lot for your reply.
>
> Let's enumerate some points:
>
> 1 - A hash temp table doesn't solve my problem because since
> I "expect" my process will stop (because of a dealock) I
> need the data persist among every stored procedure
> executions (in my production stored procedure, the process
> of select and insert data in t1 table will take a lot of
> time and I'am using this table to avoid this work in every
> reexecution).
> 2 - See if my understanding about your explanation and
> Sybase compilation is correct:
> a) during normalization process, when ASE sees a "create
> table" statement in a stored procedure, it doesn't resolve
> the table name immediately (assuming it's not fully
> qualified as create table tempdb..tb1). Then it creates a
> temporary table to aid in parse time and then drops it
> automatically at the end (I think in parse tree anyway there
> is an indication that this object should be resolved in
> runtime).
> b) other references to tempdb..tb1 are resolved using the
> object id from the "parse time created temporary table". In
> the case above, since the login creating the stored
> procedure is dbo in tempdb (login user1), the object is
> implicitly qualified and resolved as "tempdb.dbo.tb1".
> c) during execution, the create table statement creates a
> table qualified as "tempdb.guest.tb1" (the login user2
> running it is guest in tempdb) and it forces a reresolution
> of entire stored procedure (all others references to
> tempdb..tb1 are now resolved as tempdb.guest.tb1) and it
> works fine. At end, if there is no errors, tempdb.guest.tb1
> is dropped, otherwise it remains waiting the following
> executions. If I'll reexecute the procedure it should be
> resolved again according the current execution user (and
> depending the return status from previous execution, table
> tempdb.guest.tb1 still exists).
>
> If my understanding is correct, I still can't see why this
> error just occurs if the preceding execution ended in an
> unhandled way (as I've stated above, when it first ends
> without errors or with a return due a handled error, the
> following executions doesn't show the error 267).
>
> P.S.: Even dropping and recreating the stored procedure like
> above after error 267 (without dropping tempdb.guest.tb1)
> solves the problem (I must drop tempdb.guest.tb1 to
> reexecute stored procedure without any errors).
>
> Greetings and waiting for your comments.

Tried to simulate your problem and it looks like it has to do with object
ownership . When I execute the proc with user2 and break it in between
second execution fails

but same works with user1 , apparently you need to create proc with user2
in order to get around this or do what Bret has suggested in his earlier
mail





another point - In beginning of the proc you have code as

create proc stproc1 as
begin
create table tempdb..tb1
(c1 int)



this means every time when proc executes, you will have error as object
already exists, unless you explicitly drop it .



-HTH

Manish Negandhi

[TeamSybase]