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.

Msg 225 without object name

4 posts in General Discussion Last posting was on 2012-01-06 08:20:23.0Z
Eisen Posted on 2011-12-31 14:18:40.0Z
Sender: 17ef.4eff100f.1804289383@sybase.com
From: Eisen
Newsgroups: sybase.public.ase.general
Subject: Msg 225 without object name
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4eff19c0.19f5.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 31 Dec 2011 06:18:40 -0800
X-Trace: forums-1-dub 1325341120 172.20.134.41 (31 Dec 2011 06:18:40 -0800)
X-Original-Trace: 31 Dec 2011 06:18:40 -0800, 172.20.134.41
Lines: 81
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30801
Article PK: 73690

Dear all
I don't know if it's a ASE or Rep issue. But since this
error raised from an ASE so I post it here first. Please
have a check. Thanks.
the details:
1. I built a MSA environment to replicate DB of A.a to B.a
2. I found some procedures with dynamic DDL inside have
issue with DDL replication and this procedure has temp
tables and "select * into" inside so that this SP can't be
replicated as a function. To support those dynamic DDL, I
made a simple procedure p_ddl and mark it with sp_setrepproc
p_ddl,'function' to transfer ddl only.

create procedure p_ddl
@pvarcmd varchar(16384)
as
begin
execute(@pvarcmd)
end
go

As I test this procedure individualy, it works fine.
3. Then I tried with a new SP named p_test_wj3 as:
create procedure p_test_wj3
as
begin
declare @cmd varchar(5000)
begin tran
select @cmd="if (object_id('test_wj1')<>null) drop
table test_wj1"
exec p_ddl @cmd
if exists(select 1 from tb_wj_debuglist where
procname='0roll')
begin
print '0roll'
rollback tran
return
end
else save tran tr1
select @cmd='create table test_wj1 (id int,v
varchar(10))'
exec p_ddl @cmd
if exists(select 1 from tb_wj_debuglist where
procname='1roll')
begin
print '1roll'
rollback tran
return
end
else save tran tr2
select @cmd='alter table test_wj1 add constraint
pk_test_wj1 primary key (id)'
exec p_ddl @cmd
if exists(select 1 from tb_wj_debuglist where
procname='2roll')
begin
print '2roll'
rollback tran
return
end
else save tran tr3
select @cmd='insert test_wj1 select * from
test_wj_data'
execute(@cmd)
commit tran
end
go
when I execute this procedure, it works fine on primary,
and the table test_wj1 were successfully recreated and
inserted data from test_wj_data. But on the replicate, it
failed and as I checked the error messages. I found --
Message from server: Message: 225, State 1, Severity 16
-- 'Cannot run query--referenced object (name NAME NOT
RECOVERABLE) dropped during query optimization.'.

"NAME NOT RECOVERABLE"? what does this mean? and what
made this error? Would anyone got any idea on it?
Thanks in advance for any help. Thanks a lot.

Best Regards
Eisen


Rob V Posted on 2012-01-03 16:09:09.0Z
From: Rob V <rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Reply-To: rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY
Organization: Sypron BV
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:8.0) Gecko/20111105 Thunderbird/8.0
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Msg 225 without object name
References: <4eff19c0.19f5.1681692777@sybase.com>
In-Reply-To: <4eff19c0.19f5.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: <4f032825@forums-1-dub>
Date: 3 Jan 2012 08:09:09 -0800
X-Trace: forums-1-dub 1325606949 10.22.241.152 (3 Jan 2012 08:09:09 -0800)
X-Original-Trace: 3 Jan 2012 08:09:09 -0800, vip152.sybase.com
Lines: 113
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30806
Article PK: 73694

These "name not recoverable" errors indicate that ASE has a problem
trying to figure out precisely which table you are trying to access.
This is not specific to RepServer, it occurs in ASE (RepServer is just a
client app as far as ASE is concerned).
I didn't dig through the details of your code, but this sort of error
often is related to the order in which dependent objects are
created/dropped/renamed/modified. This is a resolution-level issue (i.e.
the query tree), so creating your proc "with recompile" will likely not
help (since that affects only the plan, and not the query tree).

HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Professional DBA for Sybase ASE, IQ, Replication Server

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks & Recipes for Sybase ASE"
"The Complete Sybase IQ Quick Reference Guide" (new!)
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
Sypron B.V., The Netherlands | Chamber of Commerce 27138666
-----------------------------------------------------------------

On 31-Dec-2011 15:18, Eisen wrote:
> Dear all
> I don't know if it's a ASE or Rep issue. But since this
> error raised from an ASE so I post it here first. Please
> have a check. Thanks.
> the details:
> 1. I built a MSA environment to replicate DB of A.a to B.a
> 2. I found some procedures with dynamic DDL inside have
> issue with DDL replication and this procedure has temp
> tables and "select * into" inside so that this SP can't be
> replicated as a function. To support those dynamic DDL, I
> made a simple procedure p_ddl and mark it with sp_setrepproc
> p_ddl,'function' to transfer ddl only.
>
> create procedure p_ddl
> @pvarcmd varchar(16384)
> as
> begin
> execute(@pvarcmd)
> end
> go
>
> As I test this procedure individualy, it works fine.
> 3. Then I tried with a new SP named p_test_wj3 as:
> create procedure p_test_wj3
> as
> begin
> declare @cmd varchar(5000)
> begin tran
> select @cmd="if (object_id('test_wj1')<>null) drop
> table test_wj1"
> exec p_ddl @cmd
> if exists(select 1 from tb_wj_debuglist where
> procname='0roll')
> begin
> print '0roll'
> rollback tran
> return
> end
> else save tran tr1
> select @cmd='create table test_wj1 (id int,v
> varchar(10))'
> exec p_ddl @cmd
> if exists(select 1 from tb_wj_debuglist where
> procname='1roll')
> begin
> print '1roll'
> rollback tran
> return
> end
> else save tran tr2
> select @cmd='alter table test_wj1 add constraint
> pk_test_wj1 primary key (id)'
> exec p_ddl @cmd
> if exists(select 1 from tb_wj_debuglist where
> procname='2roll')
> begin
> print '2roll'
> rollback tran
> return
> end
> else save tran tr3
> select @cmd='insert test_wj1 select * from
> test_wj_data'
> execute(@cmd)
> commit tran
> end
> go
> when I execute this procedure, it works fine on primary,
> and the table test_wj1 were successfully recreated and
> inserted data from test_wj_data. But on the replicate, it
> failed and as I checked the error messages. I found --
> Message from server: Message: 225, State 1, Severity 16
> -- 'Cannot run query--referenced object (name NAME NOT
> RECOVERABLE) dropped during query optimization.'.
>
> "NAME NOT RECOVERABLE"? what does this mean? and what
> made this error? Would anyone got any idea on it?
> Thanks in advance for any help. Thanks a lot.
>
> Best Regards
> Eisen


Eisen Posted on 2012-01-04 08:48:56.0Z
Sender: 3ca1.4f03a39f.1804289383@sybase.com
From: Eisen
Newsgroups: sybase.public.ase.general
Subject: Re: Msg 225 without object name
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4f041278.5476.1681692777@sybase.com>
References: <4f032825@forums-1-dub>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 4 Jan 2012 00:48:56 -0800
X-Trace: forums-1-dub 1325666936 172.20.134.41 (4 Jan 2012 00:48:56 -0800)
X-Original-Trace: 4 Jan 2012 00:48:56 -0800, 172.20.134.41
Lines: 163
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30807
Article PK: 73700

Dear Rob
Thanks for your kind help. Yes. I made another test and
dumped the queue content to find out the exact sql statement
is

begin transaction
exec p_ddl @pvarcmd='if (object_id(''test_wj1'')<>null) drop
table test_wj1'
exec p_ddl @pvarcmd='create table test_wj1 (id int,v
varchar(10))'
exec p_ddl @pvarcmd='alter table test_wj1 add constraint
pk_test_wj1 primary key (id)'
insert into dbo.test_wj1 (id, v) values (1, 'haha')
insert into dbo.test_wj1 (id, v) values (2, 'hehe')
insert into dbo.test_wj1 (id, v) values (3, 'hoho')
commit transaction
go

When I execute it on ASE only, it's also the same error
raised.
But if I execute them individually --
begin transaction
go
exec p_ddl @pvarcmd='if (object_id(''test_wj1'')<>null) drop
table test_wj1'
go
exec p_ddl @pvarcmd='create table test_wj1 (id int,v
varchar(10))'
go
exec p_ddl @pvarcmd='alter table test_wj1 add constraint
pk_test_wj1 primary key (id)'
go
insert into dbo.test_wj1 (id, v) values (1, 'haha')
insert into dbo.test_wj1 (id, v) values (2, 'hehe')
insert into dbo.test_wj1 (id, v) values (3, 'hoho')
go
commit transaction
go

It runs OK... I guess it's due to the "insert" statement.

Best Regards
Eisen

> These "name not recoverable" errors indicate that ASE has
> a problem trying to figure out precisely which table you
> are trying to access. This is not specific to RepServer,
> it occurs in ASE (RepServer is just a client app as far
> as ASE is concerned). I didn't dig through the details of
> your code, but this sort of error often is related to the
> order in which dependent objects are
> created/dropped/renamed/modified. This is a
> resolution-level issue (i.e. the query tree), so creating
> your proc "with recompile" will likely not help (since
> that affects only the plan, and not the query tree).
>
> HTH,
>
> Rob V.
> ----------------------------------------------------------
> ------- Rob Verschoor
>
> Certified Professional DBA for Sybase ASE, IQ, Replication
> Server
>
> Author of Sybase books (order online at
> www.sypron.nl/shop): "Tips, Tricks & Recipes for Sybase
> ASE" "The Complete Sybase IQ Quick Reference Guide" (new!)
> "The Complete Sybase ASE Quick Reference Guide"
> "The Complete Sybase Replication Server Quick Reference
> Guide"
>
> rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter:
> @rob_verschoor Sypron B.V., The Netherlands | Chamber of
> Commerce 27138666
> ----------------------------------------------------------
> -------
>
> On 31-Dec-2011 15:18, Eisen wrote:
> > Dear all
> > I don't know if it's a ASE or Rep issue. But since
> > this error raised from an ASE so I post it here first.
> > Please have a check. Thanks.
> > the details:
> > 1. I built a MSA environment to replicate DB of A.a to
> > B.a 2. I found some procedures with dynamic DDL inside
> > have issue with DDL replication and this procedure has
> > temp tables and "select * into" inside so that this SP
> > can't be replicated as a function. To support those
> > dynamic DDL, I made a simple procedure p_ddl and mark it
> > with sp_setrepproc p_ddl,'function' to transfer ddl
> only. >
> > create procedure p_ddl
> > @pvarcmd varchar(16384)
> > as
> > begin
> > execute(@pvarcmd)
> > end
> > go
> >
> > As I test this procedure individualy, it works fine.
> > 3. Then I tried with a new SP named p_test_wj3 as:
> > create procedure p_test_wj3
> > as
> > begin
> > declare @cmd varchar(5000)
> > begin tran
> > select @cmd="if (object_id('test_wj1')<>null)
> > drop table test_wj1"
> > exec p_ddl @cmd
> > if exists(select 1 from tb_wj_debuglist where
> > procname='0roll')
> > begin
> > print '0roll'
> > rollback tran
> > return
> > end
> > else save tran tr1
> > select @cmd='create table test_wj1 (id int,v
> > varchar(10))'
> > exec p_ddl @cmd
> > if exists(select 1 from tb_wj_debuglist where
> > procname='1roll')
> > begin
> > print '1roll'
> > rollback tran
> > return
> > end
> > else save tran tr2
> > select @cmd='alter table test_wj1 add
> > constraint pk_test_wj1 primary key (id)'
> > exec p_ddl @cmd
> > if exists(select 1 from tb_wj_debuglist where
> > procname='2roll')
> > begin
> > print '2roll'
> > rollback tran
> > return
> > end
> > else save tran tr3
> > select @cmd='insert test_wj1 select * from
> > test_wj_data'
> > execute(@cmd)
> > commit tran
> > end
> > go
> > when I execute this procedure, it works fine on
> > primary, and the table test_wj1 were successfully
> > recreated and inserted data from test_wj_data. But on
> > the replicate, it failed and as I checked the error
> > messages. I found -- Message from server: Message:
> > 225, State 1, Severity 16 -- 'Cannot run
> > query--referenced object (name NAME NOT RECOVERABLE)
> dropped during query optimization.'. >
> > "NAME NOT RECOVERABLE"? what does this mean? and
> > what made this error? Would anyone got any idea on it?
> > Thanks in advance for any help. Thanks a lot.
> >
> > Best Regards
> > Eisen
>
>
>


Eisen Posted on 2012-01-06 08:20:23.0Z
Sender: 5c4f.4f0685d7.1804289383@sybase.com
From: Eisen
Newsgroups: sybase.public.ase.general
Subject: Re: Msg 225 without object name
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4f06aec7.6560.1681692777@sybase.com>
References: <4f032825@forums-1-dub>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 6 Jan 2012 00:20:23 -0800
X-Trace: forums-1-dub 1325838023 172.20.134.41 (6 Jan 2012 00:20:23 -0800)
X-Original-Trace: 6 Jan 2012 00:20:23 -0800, 172.20.134.41
Lines: 128
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30817
Article PK: 73702

Hi Rob
Thanks for your support. I made some further test and
confirmed that it really is a compiler issue in one batch
SQL statement with DDL ahead and DML after... I'm confused
if it's a bug or not... Thanks

Best Regards
Eisen

> These "name not recoverable" errors indicate that ASE has
> a problem trying to figure out precisely which table you
> are trying to access. This is not specific to RepServer,
> it occurs in ASE (RepServer is just a client app as far
> as ASE is concerned). I didn't dig through the details of
> your code, but this sort of error often is related to the
> order in which dependent objects are
> created/dropped/renamed/modified. This is a
> resolution-level issue (i.e. the query tree), so creating
> your proc "with recompile" will likely not help (since
> that affects only the plan, and not the query tree).
>
> HTH,
>
> Rob V.
> ----------------------------------------------------------
> ------- Rob Verschoor
>
> Certified Professional DBA for Sybase ASE, IQ, Replication
> Server
>
> Author of Sybase books (order online at
> www.sypron.nl/shop): "Tips, Tricks & Recipes for Sybase
> ASE" "The Complete Sybase IQ Quick Reference Guide" (new!)
> "The Complete Sybase ASE Quick Reference Guide"
> "The Complete Sybase Replication Server Quick Reference
> Guide"
>
> rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter:
> @rob_verschoor Sypron B.V., The Netherlands | Chamber of
> Commerce 27138666
> ----------------------------------------------------------
> -------
>
> On 31-Dec-2011 15:18, Eisen wrote:
> > Dear all
> > I don't know if it's a ASE or Rep issue. But since
> > this error raised from an ASE so I post it here first.
> > Please have a check. Thanks.
> > the details:
> > 1. I built a MSA environment to replicate DB of A.a to
> > B.a 2. I found some procedures with dynamic DDL inside
> > have issue with DDL replication and this procedure has
> > temp tables and "select * into" inside so that this SP
> > can't be replicated as a function. To support those
> > dynamic DDL, I made a simple procedure p_ddl and mark it
> > with sp_setrepproc p_ddl,'function' to transfer ddl
> only. >
> > create procedure p_ddl
> > @pvarcmd varchar(16384)
> > as
> > begin
> > execute(@pvarcmd)
> > end
> > go
> >
> > As I test this procedure individualy, it works fine.
> > 3. Then I tried with a new SP named p_test_wj3 as:
> > create procedure p_test_wj3
> > as
> > begin
> > declare @cmd varchar(5000)
> > begin tran
> > select @cmd="if (object_id('test_wj1')<>null)
> > drop table test_wj1"
> > exec p_ddl @cmd
> > if exists(select 1 from tb_wj_debuglist where
> > procname='0roll')
> > begin
> > print '0roll'
> > rollback tran
> > return
> > end
> > else save tran tr1
> > select @cmd='create table test_wj1 (id int,v
> > varchar(10))'
> > exec p_ddl @cmd
> > if exists(select 1 from tb_wj_debuglist where
> > procname='1roll')
> > begin
> > print '1roll'
> > rollback tran
> > return
> > end
> > else save tran tr2
> > select @cmd='alter table test_wj1 add
> > constraint pk_test_wj1 primary key (id)'
> > exec p_ddl @cmd
> > if exists(select 1 from tb_wj_debuglist where
> > procname='2roll')
> > begin
> > print '2roll'
> > rollback tran
> > return
> > end
> > else save tran tr3
> > select @cmd='insert test_wj1 select * from
> > test_wj_data'
> > execute(@cmd)
> > commit tran
> > end
> > go
> > when I execute this procedure, it works fine on
> > primary, and the table test_wj1 were successfully
> > recreated and inserted data from test_wj_data. But on
> > the replicate, it failed and as I checked the error
> > messages. I found -- Message from server: Message:
> > 225, State 1, Severity 16 -- 'Cannot run
> > query--referenced object (name NAME NOT RECOVERABLE)
> dropped during query optimization.'. >
> > "NAME NOT RECOVERABLE"? what does this mean? and
> > what made this error? Would anyone got any idea on it?
> > Thanks in advance for any help. Thanks a lot.
> >
> > Best Regards
> > Eisen
>
>
>