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.

Why the dynamic sql failed in RPC while success in local

5 posts in General Discussion Last posting was on 2011-07-29 09:08:55.0Z
Eisen Posted on 2011-07-28 08:13:15.0Z
Sender: 67fa.4e31156c.1804289383@sybase.com
From: Eisen
Newsgroups: sybase.public.ase.general
Subject: Why the dynamic sql failed in RPC while success in local
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4e311a1b.68a6.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 28 Jul 2011 01:13:15 -0700
X-Trace: forums-1-dub 1311840795 10.22.241.41 (28 Jul 2011 01:13:15 -0700)
X-Original-Trace: 28 Jul 2011 01:13:15 -0700, 10.22.241.41
Lines: 37
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30382
Article PK: 72560

Dear Sirs
Currently I need to create a proxy_table at remote site
to transfer data and drop it at the end of procedure. So I
write a procedure like

create procedure p_test
@remote varchar(30),
@db varchar(20)
as
begin
declare @cmdstr varchar(500)
select @cmdstr='create proxy_table pr_tmp at
"'+@remote+'.'+@db+'..tb"'
execute(@cmdstr)
...
select @cmdstr='drop table pr_tmp'
execute(@cmdstr)
end

I deploy it to server2 and tested it by
exec p_test 'server1','db1'
it runs successfully.
But when I using rpc to invoke it on server1 by
exec server2.db2..p_test @@servername,db_name()
it always failed in creating the proxy_table and raised
error message as --
"Msg 11204, Level 16, State 23:
Server 'sybwpmtrn00', Line 1:
There is an error in the syntax of the object definition
'"sybwpm00.cmwpmpr0..tzgt_dmu_atxr_stage"'."

I don't know why it failed in RPC while succeed in local.
Would anyone help me on this? Thanks in advance for your
help.

Best Regards
Eisen


Rob V [ Sybase ] Posted on 2011-07-28 10:57:33.0Z
From: "Rob V [ Sybase ]" <rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Reply-To: rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY
Organization: Sypron BV / TeamSybase / Sybase
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.18) Gecko/20110616 Lightning/1.0b2 Thunderbird/3.1.11
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Why the dynamic sql failed in RPC while success in local
References: <4e311a1b.68a6.1681692777@sybase.com>
In-Reply-To: <4e311a1b.68a6.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: <4e31409d$1@forums-1-dub>
Date: 28 Jul 2011 03:57:33 -0700
X-Trace: forums-1-dub 1311850653 10.22.241.152 (28 Jul 2011 03:57:33 -0700)
X-Original-Trace: 28 Jul 2011 03:57:33 -0700, vip152.sybase.com
Lines: 69
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30384
Article PK: 72563

The problem is that you're using @@servername, which won't work. You
need to define a remote server like this:

sp_addserver MYSELF, null, @@servername

...and then use 'MYSELF' instead of @@servername.

@@servername is also in sysservers, but is is a special case because it
is the local servername.

HTH,

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

Certified Sybase Professional DBA for ASE 15.0/12.5/12.0/11.5/11.0
and Replication Server 15.0.1/12.5 // TeamSybase

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks& Recipes for Sybase ASE" (ASE 15 edition)
"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 28-Jul-2011 10:13, Eisen wrote:
> Dear Sirs
> Currently I need to create a proxy_table at remote site
> to transfer data and drop it at the end of procedure. So I
> write a procedure like
>
> create procedure p_test
> @remote varchar(30),
> @db varchar(20)
> as
> begin
> declare @cmdstr varchar(500)
> select @cmdstr='create proxy_table pr_tmp at
> "'+@remote+'.'+@db+'..tb"'
> execute(@cmdstr)
> ...
> select @cmdstr='drop table pr_tmp'
> execute(@cmdstr)
> end
>
> I deploy it to server2 and tested it by
> exec p_test 'server1','db1'
> it runs successfully.
> But when I using rpc to invoke it on server1 by
> exec server2.db2..p_test @@servername,db_name()
> it always failed in creating the proxy_table and raised
> error message as --
> "Msg 11204, Level 16, State 23:
> Server 'sybwpmtrn00', Line 1:
> There is an error in the syntax of the object definition
> '"sybwpm00.cmwpmpr0..tzgt_dmu_atxr_stage"'."
>
> I don't know why it failed in RPC while succeed in local.
> Would anyone help me on this? Thanks in advance for your
> help.
>
> Best Regards
> Eisen


Eisen Posted on 2011-07-29 02:35:28.0Z
Sender: 6d44.4e3128f8.1804289383@sybase.com
From: Eisen
Newsgroups: sybase.public.ase.general
Subject: Re: Why the dynamic sql failed in RPC while success in local
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4e321c6f.69f1.1681692777@sybase.com>
References: <4e31409d$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 28 Jul 2011 19:35:28 -0700
X-Trace: forums-1-dub 1311906928 10.22.241.41 (28 Jul 2011 19:35:28 -0700)
X-Original-Trace: 28 Jul 2011 19:35:28 -0700, 10.22.241.41
Lines: 123
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30406
Article PK: 72585

Rob, Thanks a lot for you reply. I debugged my sp following
your instruction by creating procedure p_prtest on server2
--
create procedure p_prtest
@remote varchar(30),
@db varchar(30),
@tb varchar(50)
as
begin
declare @cmdstr varchar(500)
select @cmdstr='create proxy_table pr_tmp at
"'+@remote+'.'+@db+'..'+@tb+'"'
select @cmdstr
execute(@cmdstr)--proxy table creation part

select @cmdstr='select * from pr_tmp'
execute(@cmdstr)--test part

select @cmdstr='drop table pr_tmp'
execute(@cmdstr)
end
go
and on the server2, I have already used "sp_addserver
server1,ASEnterprise,server1" to add the entry for
server1,and also configured the external login for server1.
After that I tested the SP on server2 by execute--
exec p_prtest 'server1','cmwpmpr0','tb'
it runs successfully, and I can see the @cmdstr to create
proxy_table is --
create proxy_table pr_tmp at "sever1.cmwpmpr0..tb1"

Then, I login server1, execute such command --
exec server2.fawpmtr1..p_prtest 'server1','cmwpmpr0','tb'
it fails again , the error message is --
Msg 11204, Level 16, State 23:
Server 'sybwpmtrn00', Line 1:
There is an error in the syntax of the object definition
'"sybwpm00.cmwpmpr0..tzgt_dmu_atxr_stage"'.

but the debug string shows is correct, also --
create proxy_table pr_tmp at
"sybwpm00.cmwpmpr0..tzgt_dmu_atxr_stage"

Why it fails in server1? I still don't have idea. Please
help me to check it out. Thanks again.

Best Regards
Eisen

> The problem is that you're using @@servername, which won't
> work. You need to define a remote server like this:
>
> sp_addserver MYSELF, null, @@servername
>
> ...and then use 'MYSELF' instead of @@servername.
>
> @@servername is also in sysservers, but is is a special
> case because it is the local servername.
>
> HTH,
>
> Rob V.
> ----------------------------------------------------------
> ------- Rob Verschoor
>
> Certified Sybase Professional DBA for ASE
> 15.0/12.5/12.0/11.5/11.0 and Replication Server
> 15.0.1/12.5 // TeamSybase
>
> Author of Sybase books (order online at
> www.sypron.nl/shop): "Tips, Tricks& Recipes for Sybase
> ASE" (ASE 15 edition) "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 28-Jul-2011 10:13, Eisen wrote:
> > Dear Sirs
> > Currently I need to create a proxy_table at remote
> > site to transfer data and drop it at the end of
> > procedure. So I write a procedure like
> >
> > create procedure p_test
> > @remote varchar(30),
> > @db varchar(20)
> > as
> > begin
> > declare @cmdstr varchar(500)
> > select @cmdstr='create proxy_table pr_tmp at
> > "'+@remote+'.'+@db+'..tb"'
> > execute(@cmdstr)
> > ...
> > select @cmdstr='drop table pr_tmp'
> > execute(@cmdstr)
> > end
> >
> > I deploy it to server2 and tested it by
> > exec p_test 'server1','db1'
> > it runs successfully.
> > But when I using rpc to invoke it on server1 by
> > exec server2.db2..p_test @@servername,db_name()
> > it always failed in creating the proxy_table and raised
> > error message as --
> > "Msg 11204, Level 16, State 23:
> > Server 'sybwpmtrn00', Line 1:
> > There is an error in the syntax of the object definition
> > '"sybwpm00.cmwpmpr0..tzgt_dmu_atxr_stage"'."
> >
> > I don't know why it failed in RPC while succeed in
> > local. Would anyone help me on this? Thanks in advance
> > for your help.
> >
> > Best Regards
> > Eisen
>


Rob V [ Sybase ] Posted on 2011-07-29 08:10:28.0Z
From: "Rob V [ Sybase ]" <rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Reply-To: rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY
Organization: Sypron BV / TeamSybase / Sybase
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.18) Gecko/20110616 Lightning/1.0b2 Thunderbird/3.1.11
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Why the dynamic sql failed in RPC while success in local
References: <4e31409d$1@forums-1-dub> <4e321c6f.69f1.1681692777@sybase.com>
In-Reply-To: <4e321c6f.69f1.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: <4e326af4@forums-1-dub>
Date: 29 Jul 2011 01:10:28 -0700
X-Trace: forums-1-dub 1311927028 10.22.241.152 (29 Jul 2011 01:10:28 -0700)
X-Original-Trace: 29 Jul 2011 01:10:28 -0700, vip152.sybase.com
Lines: 133
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30409
Article PK: 72584

It looks like you're doing the right thing. I don't know what's going wrong.
Make sure the command succeeds without a stored proc first. There is no
real difference from running it within a stored proc or without.

R.

On 29-Jul-2011 04:35, Eisen wrote:
> Rob, Thanks a lot for you reply. I debugged my sp following
> your instruction by creating procedure p_prtest on server2
> --
> create procedure p_prtest
> @remote varchar(30),
> @db varchar(30),
> @tb varchar(50)
> as
> begin
> declare @cmdstr varchar(500)
> select @cmdstr='create proxy_table pr_tmp at
> "'+@remote+'.'+@db+'..'+@tb+'"'
> select @cmdstr
> execute(@cmdstr)--proxy table creation part
>
> select @cmdstr='select * from pr_tmp'
> execute(@cmdstr)--test part
>
> select @cmdstr='drop table pr_tmp'
> execute(@cmdstr)
> end
> go
> and on the server2, I have already used "sp_addserver
> server1,ASEnterprise,server1" to add the entry for
> server1,and also configured the external login for server1.
> After that I tested the SP on server2 by execute--
> exec p_prtest 'server1','cmwpmpr0','tb'
> it runs successfully, and I can see the @cmdstr to create
> proxy_table is --
> create proxy_table pr_tmp at "sever1.cmwpmpr0..tb1"
>
> Then, I login server1, execute such command --
> exec server2.fawpmtr1..p_prtest 'server1','cmwpmpr0','tb'
> it fails again , the error message is --
> Msg 11204, Level 16, State 23:
> Server 'sybwpmtrn00', Line 1:
> There is an error in the syntax of the object definition
> '"sybwpm00.cmwpmpr0..tzgt_dmu_atxr_stage"'.
>
> but the debug string shows is correct, also --
> create proxy_table pr_tmp at
> "sybwpm00.cmwpmpr0..tzgt_dmu_atxr_stage"
>
> Why it fails in server1? I still don't have idea. Please
> help me to check it out. Thanks again.
>
> Best Regards
> Eisen
>
>
>> The problem is that you're using @@servername, which won't
>> work. You need to define a remote server like this:
>>
>> sp_addserver MYSELF, null, @@servername
>>
>> ...and then use 'MYSELF' instead of @@servername.
>>
>> @@servername is also in sysservers, but is is a special
>> case because it is the local servername.
>>
>> HTH,
>>
>> Rob V.
>> ----------------------------------------------------------
>> ------- Rob Verschoor
>>
>> Certified Sybase Professional DBA for ASE
>> 15.0/12.5/12.0/11.5/11.0 and Replication Server
>> 15.0.1/12.5 // TeamSybase
>>
>> Author of Sybase books (order online at
>> www.sypron.nl/shop): "Tips, Tricks& Recipes for Sybase
>> ASE" (ASE 15 edition) "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 28-Jul-2011 10:13, Eisen wrote:
>>> Dear Sirs
>>> Currently I need to create a proxy_table at remote
>>> site to transfer data and drop it at the end of
>>> procedure. So I write a procedure like
>>>
>>> create procedure p_test
>>> @remote varchar(30),
>>> @db varchar(20)
>>> as
>>> begin
>>> declare @cmdstr varchar(500)
>>> select @cmdstr='create proxy_table pr_tmp at
>>> "'+@remote+'.'+@db+'..tb"'
>>> execute(@cmdstr)
>>> ...
>>> select @cmdstr='drop table pr_tmp'
>>> execute(@cmdstr)
>>> end
>>>
>>> I deploy it to server2 and tested it by
>>> exec p_test 'server1','db1'
>>> it runs successfully.
>>> But when I using rpc to invoke it on server1 by
>>> exec server2.db2..p_test @@servername,db_name()
>>> it always failed in creating the proxy_table and raised
>>> error message as --
>>> "Msg 11204, Level 16, State 23:
>>> Server 'sybwpmtrn00', Line 1:
>>> There is an error in the syntax of the object definition
>>> '"sybwpm00.cmwpmpr0..tzgt_dmu_atxr_stage"'."
>>>
>>> I don't know why it failed in RPC while succeed in
>>> local. Would anyone help me on this? Thanks in advance
>>> for your help.
>>>
>>> Best Regards
>>> Eisen


Eisen Posted on 2011-07-29 09:08:55.0Z
Sender: a99.4e32542d.1804289383@sybase.com
From: Eisen
Newsgroups: sybase.public.ase.general
Subject: Re: Why the dynamic sql failed in RPC while success in local
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4e3278a7.20fa.1681692777@sybase.com>
References: <4e326af4@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 29 Jul 2011 02:08:55 -0700
X-Trace: forums-1-dub 1311930535 10.22.241.41 (29 Jul 2011 02:08:55 -0700)
X-Original-Trace: 29 Jul 2011 02:08:55 -0700, 10.22.241.41
Lines: 142
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30411
Article PK: 72590

Thanks Rob.
I tested it again on ASE12.5.4 ESD#4 and ASE15.0.2 ESD#5
-- all successful, but failed on ASE15.0.3 ESD#4. Maybe it's
a version bug. I have reported it to sybase team. Thanks
again for your kindness and help. ^_^
Best Regards
Eisen

> It looks like you're doing the right thing. I don't know
> what's going wrong. Make sure the command succeeds without
> a stored proc first. There is no real difference from
> running it within a stored proc or without.
>
> R.
>
>
>
> On 29-Jul-2011 04:35, Eisen wrote:
> > Rob, Thanks a lot for you reply. I debugged my sp
> > following your instruction by creating procedure
> > p_prtest on server2 --
> > create procedure p_prtest
> > @remote varchar(30),
> > @db varchar(30),
> > @tb varchar(50)
> > as
> > begin
> > declare @cmdstr varchar(500)
> > select @cmdstr='create proxy_table pr_tmp at
> > "'+@remote+'.'+@db+'..'+@tb+'"'
> > select @cmdstr
> > execute(@cmdstr)--proxy table creation part
> >
> > select @cmdstr='select * from pr_tmp'
> > execute(@cmdstr)--test part
> >
> > select @cmdstr='drop table pr_tmp'
> > execute(@cmdstr)
> > end
> > go
> > and on the server2, I have already used "sp_addserver
> > server1,ASEnterprise,server1" to add the entry for
> > server1,and also configured the external login for
> > server1. After that I tested the SP on server2 by
> > execute-- exec p_prtest 'server1','cmwpmpr0','tb'
> > it runs successfully, and I can see the @cmdstr to
> > create proxy_table is --
> > create proxy_table pr_tmp at "sever1.cmwpmpr0..tb1"
> >
> > Then, I login server1, execute such command --
> > exec server2.fawpmtr1..p_prtest 'server1','cmwpmpr0'
> > ,'tb' it fails again , the error message is --
> > Msg 11204, Level 16, State 23:
> > Server 'sybwpmtrn00', Line 1:
> > There is an error in the syntax of the object definition
> > '"sybwpm00.cmwpmpr0..tzgt_dmu_atxr_stage"'.
> >
> > but the debug string shows is correct, also --
> > create proxy_table pr_tmp at
> > "sybwpm00.cmwpmpr0..tzgt_dmu_atxr_stage"
> >
> > Why it fails in server1? I still don't have idea. Please
> > help me to check it out. Thanks again.
> >
> > Best Regards
> > Eisen
> >
> >
> >> The problem is that you're using @@servername, which
> won't >> work. You need to define a remote server like
> this: >>
> >> sp_addserver MYSELF, null, @@servername
> >>
> >> ...and then use 'MYSELF' instead of @@servername.
> >>
> >> @@servername is also in sysservers, but is is a special
> >> case because it is the local servername.
> >>
> >> HTH,
> >>
> >> Rob V.
> >>
> ----------------------------------------------------------
> >> ------- Rob Verschoor >>
> >> Certified Sybase Professional DBA for ASE
> >> 15.0/12.5/12.0/11.5/11.0 and Replication Server
> >> 15.0.1/12.5 // TeamSybase
> >>
> >> Author of Sybase books (order online at
> >> www.sypron.nl/shop): "Tips, Tricks& Recipes for
> Sybase >> ASE" (ASE 15 edition) "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 28-Jul-2011 10:13, Eisen wrote:
> >>> Dear Sirs
> >>> Currently I need to create a proxy_table at
> remote >>> site to transfer data and drop it at the end of
> >>> procedure. So I write a procedure like
> >>>
> >>> create procedure p_test
> >>> @remote varchar(30),
> >>> @db varchar(20)
> >>> as
> >>> begin
> >>> declare @cmdstr varchar(500)
> >>> select @cmdstr='create proxy_table pr_tmp at
> >>> "'+@remote+'.'+@db+'..tb"'
> >>> execute(@cmdstr)
> >>> ...
> >>> select @cmdstr='drop table pr_tmp'
> >>> execute(@cmdstr)
> >>> end
> >>>
> >>> I deploy it to server2 and tested it by
> >>> exec p_test 'server1','db1'
> >>> it runs successfully.
> >>> But when I using rpc to invoke it on server1 by
> >>> exec server2.db2..p_test @@servername,db_name()
> >>> it always failed in creating the proxy_table and
> raised >>> error message as --
> >>> "Msg 11204, Level 16, State 23:
> >>> Server 'sybwpmtrn00', Line 1:
> >>> There is an error in the syntax of the object
> definition >>>
> '"sybwpm00.cmwpmpr0..tzgt_dmu_atxr_stage"'." >>>
> >>> I don't know why it failed in RPC while succeed in
> >>> local. Would anyone help me on this? Thanks in advance
> >>> for your help.
> >>>
> >>> Best Regards
> >>> Eisen
>
>