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.

DDL in Dynamic SQL of Procedure can't be replicated

6 posts in General Discussion Last posting was on 2011-12-31 13:42:07.0Z
Eisen Posted on 2011-12-07 06:52:28.0Z
Sender: ace.4eddd306.1804289383@sybase.com
From: Eisen
Newsgroups: sybase.public.rep-server
Subject: DDL in Dynamic SQL of Procedure can't be replicated
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4edf0d2c.c9e.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 6 Dec 2011 22:52:28 -0800
X-Trace: forums-1-dub 1323240748 10.22.241.41 (6 Dec 2011 22:52:28 -0800)
X-Original-Trace: 6 Dec 2011 22:52:28 -0800, 10.22.241.41
Lines: 38
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.rep-server:9101
Article PK: 870263

Dear all
I built a MSA system with ddl replicated. It works fine
for several months. But today an error raised -- "Can't drop
table tmpCLM248lbl_C". As I checked, I found on the primary
side a new deployed procedure made this error. So I made
some test --
on the Primary side, I directly execute such commands --
declare @cmd varchar(5000)
select @cmd='if (object_id("test_wj1")<>null) drop table
test_wj1'
execute(@cmd)
select @cmd='create table test_wj1(id int)'
execute(@cmd)

it works fine, and I found on the replicate site , the table
of test_wj1 is droped and recreated successfully.

But when I made it in a procedure:
create procedure p_testwj1
as
begin
declare @cmd varchar(5000)
select @cmd='if (object_id("test_wj1")<>null) drop table
test_wj1'
execute(@cmd)
select @cmd='create table test_wj1(id int)'
execute(@cmd)
end
go

I found execute this SP only can drop and recreate test_wj1
on primary site, these DDL in dynamic SQL can't be
replicated to replicate site. What make this happen? If
there any solution to set up the MSA system to support such
dynamic DDL in procedures? Thanks in advance for any help.

Best Regards
Eisen


Manish Negandhi [TeamSybase] Posted on 2011-12-09 03:13:44.0Z
From: "Manish Negandhi [TeamSybase]" <nospam_negandhi.manish@gmail.com>
Newsgroups: sybase.public.rep-server
References: <4edf0d2c.c9e.1681692777@sybase.com>
Subject: Re: DDL in Dynamic SQL of Procedure can't be replicated
Lines: 50
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: <4ee17ce8$1@forums-1-dub>
Date: 8 Dec 2011 19:13:44 -0800
X-Trace: forums-1-dub 1323400424 10.22.241.152 (8 Dec 2011 19:13:44 -0800)
X-Original-Trace: 8 Dec 2011 19:13:44 -0800, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.rep-server:9106
Article PK: 870269

Interesting..can you try using dynamic sql for something like select or
dummy insert etc instead of 'drop table' to check if its working with
stored proc

Manish Negandhi
[TeamSybase]

<Eisen> wrote in message news:4edf0d2c.c9e.1681692777@sybase.com...
> Dear all
> I built a MSA system with ddl replicated. It works fine
> for several months. But today an error raised -- "Can't drop
> table tmpCLM248lbl_C". As I checked, I found on the primary
> side a new deployed procedure made this error. So I made
> some test --
> on the Primary side, I directly execute such commands --
> declare @cmd varchar(5000)
> select @cmd='if (object_id("test_wj1")<>null) drop table
> test_wj1'
> execute(@cmd)
> select @cmd='create table test_wj1(id int)'
> execute(@cmd)
>
> it works fine, and I found on the replicate site , the table
> of test_wj1 is droped and recreated successfully.
>
> But when I made it in a procedure:
> create procedure p_testwj1
> as
> begin
> declare @cmd varchar(5000)
> select @cmd='if (object_id("test_wj1")<>null) drop table
> test_wj1'
> execute(@cmd)
> select @cmd='create table test_wj1(id int)'
> execute(@cmd)
> end
> go
>
> I found execute this SP only can drop and recreate test_wj1
> on primary site, these DDL in dynamic SQL can't be
> replicated to replicate site. What make this happen? If
> there any solution to set up the MSA system to support such
> dynamic DDL in procedures? Thanks in advance for any help.
>
> Best Regards
> Eisen


"Mark A. Parsons" <iron_horse Posted on 2011-12-09 13:54:49.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.13) Gecko/20101207 Lightning/1.0b2 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.rep-server
Subject: Re: DDL in Dynamic SQL of Procedure can't be replicated
References: <4edf0d2c.c9e.1681692777@sybase.com> <4ee17ce8$1@forums-1-dub>
In-Reply-To: <4ee17ce8$1@forums-1-dub>
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: <4ee21329$1@forums-1-dub>
Date: 9 Dec 2011 05:54:49 -0800
X-Trace: forums-1-dub 1323438889 10.22.241.152 (9 Dec 2011 05:54:49 -0800)
X-Original-Trace: 9 Dec 2011 05:54:49 -0800, vip152.sybase.com
Lines: 77
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.rep-server:9108
Article PK: 870268

I ran into a similar situation a few months ago with a proc that was performing the following:

exec('drop view v1')
exec('create view v1 as ....')

The PDB is the active db in a warm standby setup with DDL encryption enabled.

The proc was not marked for replication.

The drop/create functioned as expected in the PDB, but only the 'create view' was being passed to the RDB. Since the
'drop view' was not being sent to the RDB the 'create view' would fail (ie, 'ERROR: view v1 already exists) and bring
down the DSI.

The feedback from Sybase tech support was that this is a known bug whereby some DDL issued from within a proc does not
get replicated, though I didn't get any details on if/when it would be fixed..

We were able to come up with a workaround for our particular situation by insuring the 'drop view' was performed outside
of the proc, while the 'create view' was left inside the proc.

I'd suggest opening a case with Sybase tech support if just to show them that there are other sites running into the
same issue.

NOTE: The above exec('drop/create view') commands are successfully replicated when issued as part of a SQL batch; it's
just when issued within a stored proc that the 'drop view' is not replicated.

On 12/08/2011 22:13, Manish Negandhi [TeamSybase] wrote:
> Interesting..can you try using dynamic sql for something like select or
> dummy insert etc instead of 'drop table' to check if its working with
> stored proc
>
> Manish Negandhi
> [TeamSybase]
>
>
>
> <Eisen> wrote in message news:4edf0d2c.c9e.1681692777@sybase.com...
>> Dear all
>> I built a MSA system with ddl replicated. It works fine
>> for several months. But today an error raised -- "Can't drop
>> table tmpCLM248lbl_C". As I checked, I found on the primary
>> side a new deployed procedure made this error. So I made
>> some test --
>> on the Primary side, I directly execute such commands --
>> declare @cmd varchar(5000)
>> select @cmd='if (object_id("test_wj1")<>null) drop table
>> test_wj1'
>> execute(@cmd)
>> select @cmd='create table test_wj1(id int)'
>> execute(@cmd)
>>
>> it works fine, and I found on the replicate site , the table
>> of test_wj1 is droped and recreated successfully.
>>
>> But when I made it in a procedure:
>> create procedure p_testwj1
>> as
>> begin
>> declare @cmd varchar(5000)
>> select @cmd='if (object_id("test_wj1")<>null) drop table
>> test_wj1'
>> execute(@cmd)
>> select @cmd='create table test_wj1(id int)'
>> execute(@cmd)
>> end
>> go
>>
>> I found execute this SP only can drop and recreate test_wj1
>> on primary site, these DDL in dynamic SQL can't be
>> replicated to replicate site. What make this happen? If
>> there any solution to set up the MSA system to support such
>> dynamic DDL in procedures? Thanks in advance for any help.
>>
>> Best Regards
>> Eisen
>
>


Eisen Posted on 2011-12-13 08:16:02.0Z
Sender: 2437.4ee70682.1804289383@sybase.com
From: Eisen
Newsgroups: sybase.public.rep-server
Subject: Re: DDL in Dynamic SQL of Procedure can't be replicated
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4ee709c2.2579.1681692777@sybase.com>
References: <4ee21329$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 13 Dec 2011 00:16:02 -0800
X-Trace: forums-1-dub 1323764162 10.22.241.41 (13 Dec 2011 00:16:02 -0800)
X-Original-Trace: 13 Dec 2011 00:16:02 -0800, 10.22.241.41
Lines: 97
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.rep-server:9113
Article PK: 870275

Hi Mark and Manish
Thanks for your reply. OK. This case has been commited to
Sybase Tech support, but he insist on this is only features
of RS not issue and always use "go back and check manual
yourself" to answer me...

Best Regards
Eisen

> I ran into a similar situation a few months ago with a
> proc that was performing the following:
>
> exec('drop view v1')
> exec('create view v1 as ....')
>
> The PDB is the active db in a warm standby setup with DDL
> encryption enabled.
>
> The proc was not marked for replication.
>
> The drop/create functioned as expected in the PDB, but
> only the 'create view' was being passed to the RDB. Since
> the 'drop view' was not being sent to the RDB the 'create
> view' would fail (ie, 'ERROR: view v1 already exists) and
> bring down the DSI.
>
> The feedback from Sybase tech support was that this is a
> known bug whereby some DDL issued from within a proc does
> not get replicated, though I didn't get any details on
> if/when it would be fixed..
>
> We were able to come up with a workaround for our
> particular situation by insuring the 'drop view' was
> performed outside of the proc, while the 'create view'
> was left inside the proc.
>
> I'd suggest opening a case with Sybase tech support if
> just to show them that there are other sites running into
> the same issue.
>
> NOTE: The above exec('drop/create view') commands are
> successfully replicated when issued as part of a SQL batch
> ; it's just when issued within a stored proc that the
> 'drop view' is not replicated.
>
>
> On 12/08/2011 22:13, Manish Negandhi [TeamSybase] wrote:
> > Interesting..can you try using dynamic sql for something
> > like select or dummy insert etc instead of 'drop table'
> > to check if its working with stored proc
> >
> > Manish Negandhi
> > [TeamSybase]
> >
> >
> >
> > <Eisen> wrote in message
> news:4edf0d2c.c9e.1681692777@sybase.com... >> Dear all
> >> I built a MSA system with ddl replicated. It works
> fine >> for several months. But today an error raised --
> "Can't drop >> table tmpCLM248lbl_C". As I checked, I
> found on the primary >> side a new deployed procedure made
> this error. So I made >> some test --
> >> on the Primary side, I directly execute such commands
> -- >> declare @cmd varchar(5000)
> >> select @cmd='if (object_id("test_wj1")<>null) drop
> table >> test_wj1'
> >> execute(@cmd)
> >> select @cmd='create table test_wj1(id int)'
> >> execute(@cmd)
> >>
> >> it works fine, and I found on the replicate site , the
> table >> of test_wj1 is droped and recreated successfully.
> >>
> >> But when I made it in a procedure:
> >> create procedure p_testwj1
> >> as
> >> begin
> >> declare @cmd varchar(5000)
> >> select @cmd='if (object_id("test_wj1")<>null) drop
> table >> test_wj1'
> >> execute(@cmd)
> >> select @cmd='create table test_wj1(id int)'
> >> execute(@cmd)
> >> end
> >> go
> >>
> >> I found execute this SP only can drop and recreate
> test_wj1 >> on primary site, these DDL in dynamic SQL
> can't be >> replicated to replicate site. What make this
> happen? If >> there any solution to set up the MSA system
> to support such >> dynamic DDL in procedures? Thanks in
> advance for any help. >>
> >> Best Regards
> >> Eisen
> >
> >


Manish Negandhi [TeamSybase] Posted on 2011-12-27 16:05:03.0Z
From: "Manish Negandhi [TeamSybase]" <nospam_negandhi.manish@gmail.com>
Newsgroups: sybase.public.rep-server
References: <4ee21329$1@forums-1-dub> <4ee709c2.2579.1681692777@sybase.com>
Subject: Re: DDL in Dynamic SQL of Procedure can't be replicated
Lines: 133
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: <4ef9ecaf@forums-1-dub>
Date: 27 Dec 2011 08:05:03 -0800
X-Trace: forums-1-dub 1325001903 10.22.241.152 (27 Dec 2011 08:05:03 -0800)
X-Original-Trace: 27 Dec 2011 08:05:03 -0800, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.rep-server:9122
Article PK: 870285

Hello Eisen

Can you try marking the proc execlusively for replication and see if that
works, for example

create procedure p_testwj1
as
begin
declare @cmd varchar(5000)
select @cmd='if (object_id("test_wj1")<>null) drop table
test_wj1'
execute(@cmd)
select @cmd='create table test_wj1(id int)'
execute(@cmd)
end
go

sp_setrepproc p_testwj1,"function"
go

exec p_testwj1
go

This actually works as expected, table gets recreated each time proc is
executed , it worked for me

-HTH
Manish Negandhi
[TeamSybase]

<Eisen> wrote in message news:4ee709c2.2579.1681692777@sybase.com...
> Hi Mark and Manish
> Thanks for your reply. OK. This case has been commited to
> Sybase Tech support, but he insist on this is only features
> of RS not issue and always use "go back and check manual
> yourself" to answer me...
>
> Best Regards
> Eisen
>
>> I ran into a similar situation a few months ago with a
>> proc that was performing the following:
>>
>> exec('drop view v1')
>> exec('create view v1 as ....')
>>
>> The PDB is the active db in a warm standby setup with DDL
>> encryption enabled.
>>
>> The proc was not marked for replication.
>>
>> The drop/create functioned as expected in the PDB, but
>> only the 'create view' was being passed to the RDB. Since
>> the 'drop view' was not being sent to the RDB the 'create
>> view' would fail (ie, 'ERROR: view v1 already exists) and
>> bring down the DSI.
>>
>> The feedback from Sybase tech support was that this is a
>> known bug whereby some DDL issued from within a proc does
>> not get replicated, though I didn't get any details on
>> if/when it would be fixed..
>>
>> We were able to come up with a workaround for our
>> particular situation by insuring the 'drop view' was
>> performed outside of the proc, while the 'create view'
>> was left inside the proc.
>>
>> I'd suggest opening a case with Sybase tech support if
>> just to show them that there are other sites running into
>> the same issue.
>>
>> NOTE: The above exec('drop/create view') commands are
>> successfully replicated when issued as part of a SQL batch
>> ; it's just when issued within a stored proc that the
>> 'drop view' is not replicated.
>>
>>
>> On 12/08/2011 22:13, Manish Negandhi [TeamSybase] wrote:
>> > Interesting..can you try using dynamic sql for something
>> > like select or dummy insert etc instead of 'drop table'
>> > to check if its working with stored proc
>> >
>> > Manish Negandhi
>> > [TeamSybase]
>> >
>> >
>> >
>> > <Eisen> wrote in message
>> news:4edf0d2c.c9e.1681692777@sybase.com... >> Dear all
>> >> I built a MSA system with ddl replicated. It works
>> fine >> for several months. But today an error raised --
>> "Can't drop >> table tmpCLM248lbl_C". As I checked, I
>> found on the primary >> side a new deployed procedure made
>> this error. So I made >> some test --
>> >> on the Primary side, I directly execute such commands
>> -- >> declare @cmd varchar(5000)
>> >> select @cmd='if (object_id("test_wj1")<>null) drop
>> table >> test_wj1'
>> >> execute(@cmd)
>> >> select @cmd='create table test_wj1(id int)'
>> >> execute(@cmd)
>> >>
>> >> it works fine, and I found on the replicate site , the
>> table >> of test_wj1 is droped and recreated successfully.
>> >>
>> >> But when I made it in a procedure:
>> >> create procedure p_testwj1
>> >> as
>> >> begin
>> >> declare @cmd varchar(5000)
>> >> select @cmd='if (object_id("test_wj1")<>null) drop
>> table >> test_wj1'
>> >> execute(@cmd)
>> >> select @cmd='create table test_wj1(id int)'
>> >> execute(@cmd)
>> >> end
>> >> go
>> >>
>> >> I found execute this SP only can drop and recreate
>> test_wj1 >> on primary site, these DDL in dynamic SQL
>> can't be >> replicated to replicate site. What make this
>> happen? If >> there any solution to set up the MSA system
>> to support such >> dynamic DDL in procedures? Thanks in
>> advance for any help. >>
>> >> Best Regards
>> >> Eisen
>> >
>> >


Eisen Posted on 2011-12-31 13:42:07.0Z
Sender: 17ef.4eff100f.1804289383@sybase.com
From: Eisen
Newsgroups: sybase.public.rep-server
Subject: Re: DDL in Dynamic SQL of Procedure can't be replicated
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4eff112f.181e.1681692777@sybase.com>
References: <4ef9ecaf@forums-1-dub>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 31 Dec 2011 05:42:07 -0800
X-Trace: forums-1-dub 1325338927 172.20.134.41 (31 Dec 2011 05:42:07 -0800)
X-Original-Trace: 31 Dec 2011 05:42:07 -0800, 172.20.134.41
Lines: 151
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.rep-server:9123
Article PK: 870287

Hi Manish
Thanks a lot. I tried this, but still failed... the error
messages is --
E. 2011/12/31 21:40:22. ERROR #1028 DSI EXEC(110(1)
sybwpmdr00.fawpmmo0xc) - dsiqmint.c(4292)
Message from server: Message: 225, State 1, Severity
16 -- 'Cannot run query--referenced object (name NAME NOT
RECOVERABLE) dropped during query optimization.
'.
Anyway, I wrote a procedure which can read the table
structure from system tables and form a new DDL to create
table dynamicly and then I make a function replication
definition for this procedure individualy. Now, the issue is
solved. Thanks a lot for your help. :-)

Best Regards
Eisen

> Hello Eisen
>
> Can you try marking the proc execlusively for replication
> and see if that works, for example
>
> create procedure p_testwj1
> as
> begin
> declare @cmd varchar(5000)
> select @cmd='if (object_id("test_wj1")<>null) drop table
> test_wj1'
> execute(@cmd)
> select @cmd='create table test_wj1(id int)'
> execute(@cmd)
> end
> go
>
> sp_setrepproc p_testwj1,"function"
> go
>
> exec p_testwj1
> go
>
> This actually works as expected, table gets recreated each
> time proc is executed , it worked for me
>
> -HTH
> Manish Negandhi
> [TeamSybase]
>
>
>
>
> <Eisen> wrote in message
> > news:4ee709c2.2579.1681692777@sybase.com... Hi Mark and
> > Manish Thanks for your reply. OK. This case has been
> > commited to Sybase Tech support, but he insist on this
> > is only features of RS not issue and always use "go back
> > and check manual yourself" to answer me...
> >
> > Best Regards
> > Eisen
> >
> >> I ran into a similar situation a few months ago with a
> >> proc that was performing the following:
> >>
> >> exec('drop view v1')
> >> exec('create view v1 as ....')
> >>
> >> The PDB is the active db in a warm standby setup with
> DDL >> encryption enabled.
> >>
> >> The proc was not marked for replication.
> >>
> >> The drop/create functioned as expected in the PDB, but
> >> only the 'create view' was being passed to the RDB.
> Since >> the 'drop view' was not being sent to the RDB
> the 'create >> view' would fail (ie, 'ERROR: view v1
> already exists) and >> bring down the DSI.
> >>
> >> The feedback from Sybase tech support was that this is
> a >> known bug whereby some DDL issued from within a proc
> does >> not get replicated, though I didn't get any
> details on >> if/when it would be fixed..
> >>
> >> We were able to come up with a workaround for our
> >> particular situation by insuring the 'drop view' was
> >> performed outside of the proc, while the 'create view'
> >> was left inside the proc.
> >>
> >> I'd suggest opening a case with Sybase tech support if
> >> just to show them that there are other sites running
> into >> the same issue.
> >>
> >> NOTE: The above exec('drop/create view') commands are
> >> successfully replicated when issued as part of a SQL
> batch >> ; it's just when issued within a stored proc
> that the >> 'drop view' is not replicated.
> >>
> >>
> >> On 12/08/2011 22:13, Manish Negandhi [TeamSybase]
> wrote: >> > Interesting..can you try using dynamic sql for
> something >> > like select or dummy insert etc instead of
> 'drop table' >> > to check if its working with stored
> proc >> >
> >> > Manish Negandhi
> >> > [TeamSybase]
> >> >
> >> >
> >> >
> >> > <Eisen> wrote in message
> >> news:4edf0d2c.c9e.1681692777@sybase.com... >> Dear all
> >> >> I built a MSA system with ddl replicated. It
> works >> fine >> for several months. But today an error
> raised -- >> "Can't drop >> table tmpCLM248lbl_C". As I
> checked, I >> found on the primary >> side a new deployed
> procedure made >> this error. So I made >> some test --
> >> >> on the Primary side, I directly execute such
> commands >> -- >> declare @cmd varchar(5000)
> >> >> select @cmd='if (object_id("test_wj1")<>null) drop
> >> table >> test_wj1'
> >> >> execute(@cmd)
> >> >> select @cmd='create table test_wj1(id int)'
> >> >> execute(@cmd)
> >> >>
> >> >> it works fine, and I found on the replicate site ,
> the >> table >> of test_wj1 is droped and recreated
> successfully. >> >>
> >> >> But when I made it in a procedure:
> >> >> create procedure p_testwj1
> >> >> as
> >> >> begin
> >> >> declare @cmd varchar(5000)
> >> >> select @cmd='if (object_id("test_wj1")<>null) drop
> >> table >> test_wj1'
> >> >> execute(@cmd)
> >> >> select @cmd='create table test_wj1(id int)'
> >> >> execute(@cmd)
> >> >> end
> >> >> go
> >> >>
> >> >> I found execute this SP only can drop and recreate
> >> test_wj1 >> on primary site, these DDL in dynamic SQL
> >> can't be >> replicated to replicate site. What make
> this >> happen? If >> there any solution to set up the MSA
> system >> to support such >> dynamic DDL in procedures?
> Thanks in >> advance for any help. >>
> >> >> Best Regards
> >> >> Eisen
> >> >
> >> >
>
>