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.

How to replicate a SP has "select into" inside?

7 posts in General Discussion Last posting was on 2011-12-23 06:06:18.0Z
Eisen Posted on 2011-12-22 03:39:07.0Z
Sender: 1eb5.4eefed91.1804289383@sybase.com
From: Eisen
Newsgroups: sybase.public.rep-server
Subject: How to replicate a SP has "select into" inside?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4ef2a65b.4317.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 21 Dec 2011 19:39:07 -0800
X-Trace: forums-1-dub 1324525147 172.20.134.41 (21 Dec 2011 19:39:07 -0800)
X-Original-Trace: 21 Dec 2011 19:39:07 -0800, 172.20.134.41
Lines: 19
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.rep-server:9115
Article PK: 870277

Dear all
Since the RS can't replicate the DDL statements inside
procedure in MSA, I have to replicate the execution of
procedures of this kind. But soon I found issue raised --
all the SPs have "select into..." would be failed while
replicating, the error is all like "Message from server:
Message: 226, State 1, Severity 16 -- 'SELECT INTO command
not allowed within multi-statement transaction."
I think it's due to the RS will pack the procedure
execution with transaction automatically. but this prevent
the "select into" inside these procedures -- even only
select into a temp table. As we all know that the "select
into" a temp table is very common in T-SQL programming. I
can't remove this from all the procedures.
I have totally no idea on this issue now. Would anyone
kind to help? Thanks.

Best Regards
Eisen


"Mark A. Parsons" <iron_horse Posted on 2011-12-22 04:33:05.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: How to replicate a SP has "select into" inside?
References: <4ef2a65b.4317.1681692777@sybase.com>
In-Reply-To: <4ef2a65b.4317.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: <4ef2b301@forums-1-dub>
Date: 21 Dec 2011 20:33:05 -0800
X-Trace: forums-1-dub 1324528385 10.22.241.152 (21 Dec 2011 20:33:05 -0800)
X-Original-Trace: 21 Dec 2011 20:33:05 -0800, vip152.sybase.com
Lines: 31
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.rep-server:9116
Article PK: 870279

Assuming all of your select/into commands are for creating #temp tables (eg, you're not using select/into to create
permanent tables in non-temporary databases) ...

Create a new user-defined temporary database on the RDS and configure it with 'ddl in tran' enabled.

Bind your DSI's maintuser login to this new user-defined temporary database.

As long as the maintuser login is the only session using the user-defined temporary database there should be no issues
with excessive blocking on the system tables.

On 12/21/2011 22:39, Eisen wrote:
> Dear all
> Since the RS can't replicate the DDL statements inside
> procedure in MSA, I have to replicate the execution of
> procedures of this kind. But soon I found issue raised --
> all the SPs have "select into..." would be failed while
> replicating, the error is all like "Message from server:
> Message: 226, State 1, Severity 16 -- 'SELECT INTO command
> not allowed within multi-statement transaction."
> I think it's due to the RS will pack the procedure
> execution with transaction automatically. but this prevent
> the "select into" inside these procedures -- even only
> select into a temp table. As we all know that the "select
> into" a temp table is very common in T-SQL programming. I
> can't remove this from all the procedures.
> I have totally no idea on this issue now. Would anyone
> kind to help? Thanks.
>
> Best Regards
> Eisen


Eisen Posted on 2011-12-22 05:04:47.0Z
Sender: 1eb5.4eefed91.1804289383@sybase.com
From: Eisen
Newsgroups: sybase.public.rep-server
Subject: Re: How to replicate a SP has "select into" inside?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4ef2ba6f.47dc.1681692777@sybase.com>
References: <4ef2b301@forums-1-dub>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 21 Dec 2011 21:04:47 -0800
X-Trace: forums-1-dub 1324530287 172.20.134.41 (21 Dec 2011 21:04:47 -0800)
X-Original-Trace: 21 Dec 2011 21:04:47 -0800, 172.20.134.41
Lines: 56
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.rep-server:9117
Article PK: 870280

Hi Mark
Thanks a lot for your help. Anyway I thought it before,
but in practice, I can't do that, for most of these "select
into..." were used in dynamic SQL to create temp tables with
different structure -- eg.
declare @cmd='select * into
'+db_name(tempdb_id())+'..tmptb_'+newid()+' from '+@tbname
execute(@cmd)
...
As each time, the @tbname is different, so I can't create
table before, and I can only use "select into" to create
this table.
Is there any other solution? Thanks
ps: I'm thinking-- if I use xp_cmdshell inside transaction
to create a new session maybe it can solve this but it's
also another problem on security for the executor will not
be the real executor of this procedure...

Best Regards
Eisen

> Assuming all of your select/into commands are for creating
> #temp tables (eg, you're not using select/into to create
> permanent tables in non-temporary databases) ...
>
> Create a new user-defined temporary database on the RDS
> and configure it with 'ddl in tran' enabled.
>
> Bind your DSI's maintuser login to this new user-defined
> temporary database.
>
> As long as the maintuser login is the only session using
> the user-defined temporary database there should be no
> issues with excessive blocking on the system tables.
>
>
> On 12/21/2011 22:39, Eisen wrote:
> > Dear all
> > Since the RS can't replicate the DDL statements
> > inside procedure in MSA, I have to replicate the
> > execution of procedures of this kind. But soon I found
> > issue raised -- all the SPs have "select into..." would
> > be failed while replicating, the error is all like
> > "Message from server: Message: 226, State 1, Severity 16
> > -- 'SELECT INTO command not allowed within
> > multi-statement transaction." I think it's due to
> > the RS will pack the procedure execution with
> > transaction automatically. but this prevent the "select
> > into" inside these procedures -- even only select into a
> > temp table. As we all know that the "select into" a temp
> > table is very common in T-SQL programming. I can't
> > remove this from all the procedures. I have totally
> > no idea on this issue now. Would anyone kind to help?
> Thanks. >
> > Best Regards
> > Eisen


"Mark A. Parsons" <iron_horse Posted on 2011-12-22 13:26:28.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: How to replicate a SP has "select into" inside?
References: <4ef2b301@forums-1-dub> <4ef2ba6f.47dc.1681692777@sybase.com>
In-Reply-To: <4ef2ba6f.47dc.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: <4ef33004$1@forums-1-dub>
Date: 22 Dec 2011 05:26:28 -0800
X-Trace: forums-1-dub 1324560388 10.22.241.152 (22 Dec 2011 05:26:28 -0800)
X-Original-Trace: 22 Dec 2011 05:26:28 -0800, vip152.sybase.com
Lines: 62
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.rep-server:9118
Article PK: 870281

Sorry, I misspoke in my earlier email ... proc replication isn't going to work for select/into.

If you're running ASE 15.0.3+ and RS 15.2+ you might try SQL statement replication, though you'll also need to change
your stored procs (eg, SQL statement replication doesn't work with cross-database references).

On 12/22/2011 00:04, Eisen wrote:
> Hi Mark
> Thanks a lot for your help. Anyway I thought it before,
> but in practice, I can't do that, for most of these "select
> into..." were used in dynamic SQL to create temp tables with
> different structure -- eg.
> declare @cmd='select * into
> '+db_name(tempdb_id())+'..tmptb_'+newid()+' from '+@tbname
> execute(@cmd)
> ...
> As each time, the @tbname is different, so I can't create
> table before, and I can only use "select into" to create
> this table.
> Is there any other solution? Thanks
> ps: I'm thinking-- if I use xp_cmdshell inside transaction
> to create a new session maybe it can solve this but it's
> also another problem on security for the executor will not
> be the real executor of this procedure...
>
> Best Regards
> Eisen
>
>> Assuming all of your select/into commands are for creating
>> #temp tables (eg, you're not using select/into to create
>> permanent tables in non-temporary databases) ...
>>
>> Create a new user-defined temporary database on the RDS
>> and configure it with 'ddl in tran' enabled.
>>
>> Bind your DSI's maintuser login to this new user-defined
>> temporary database.
>>
>> As long as the maintuser login is the only session using
>> the user-defined temporary database there should be no
>> issues with excessive blocking on the system tables.
>>
>>
>> On 12/21/2011 22:39, Eisen wrote:
>>> Dear all
>>> Since the RS can't replicate the DDL statements
>>> inside procedure in MSA, I have to replicate the
>>> execution of procedures of this kind. But soon I found
>>> issue raised -- all the SPs have "select into..." would
>>> be failed while replicating, the error is all like
>>> "Message from server: Message: 226, State 1, Severity 16
>>> -- 'SELECT INTO command not allowed within
>>> multi-statement transaction." I think it's due to
>>> the RS will pack the procedure execution with
>>> transaction automatically. but this prevent the "select
>>> into" inside these procedures -- even only select into a
>>> temp table. As we all know that the "select into" a temp
>>> table is very common in T-SQL programming. I can't
>>> remove this from all the procedures. I have totally
>>> no idea on this issue now. Would anyone kind to help?
>> Thanks.>
>>> Best Regards
>>> Eisen


Eisen Posted on 2011-12-22 13:35:30.0Z
Sender: 63aa.4ef330d1.1804289383@sybase.com
From: Eisen
Newsgroups: sybase.public.rep-server
Subject: Re: How to replicate a SP has "select into" inside?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4ef33222.640e.1681692777@sybase.com>
References: <4ef33004$1@forums-1-dub>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 22 Dec 2011 05:35:30 -0800
X-Trace: forums-1-dub 1324560930 172.20.134.41 (22 Dec 2011 05:35:30 -0800)
X-Original-Trace: 22 Dec 2011 05:35:30 -0800, 172.20.134.41
Lines: 71
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.rep-server:9119
Article PK: 870282

Hi Mark
Thanks. Would you please tell me a little more about SQL
statement replication? I don't know it before. Thanks.
Best Regards
Eisen

> Sorry, I misspoke in my earlier email ... proc replication
> isn't going to work for select/into.
>
> If you're running ASE 15.0.3+ and RS 15.2+ you might try
> SQL statement replication, though you'll also need to
> change your stored procs (eg, SQL statement replication
> doesn't work with cross-database references).
>
> On 12/22/2011 00:04, Eisen wrote:
> > Hi Mark
> > Thanks a lot for your help. Anyway I thought it
> > before, but in practice, I can't do that, for most of
> > these "select into..." were used in dynamic SQL to
> > create temp tables with different structure -- eg.
> > declare @cmd='select * into
> > '+db_name(tempdb_id())+'..tmptb_'+newid()+' from
> > '+@tbname execute(@cmd)
> > ...
> > As each time, the @tbname is different, so I can't
> > create table before, and I can only use "select into" to
> > create this table.
> > Is there any other solution? Thanks
> > ps: I'm thinking-- if I use xp_cmdshell inside
> > transaction to create a new session maybe it can solve
> > this but it's also another problem on security for the
> > executor will not be the real executor of this
> procedure... >
> > Best Regards
> > Eisen
> >
> >> Assuming all of your select/into commands are for
> creating >> #temp tables (eg, you're not using select/into
> to create >> permanent tables in non-temporary databases)
> .. >>
> >> Create a new user-defined temporary database on the RDS
> >> and configure it with 'ddl in tran' enabled.
> >>
> >> Bind your DSI's maintuser login to this new
> user-defined >> temporary database.
> >>
> >> As long as the maintuser login is the only session
> using >> the user-defined temporary database there should
> be no >> issues with excessive blocking on the system
> tables. >>
> >>
> >> On 12/21/2011 22:39, Eisen wrote:
> >>> Dear all
> >>> Since the RS can't replicate the DDL statements
> >>> inside procedure in MSA, I have to replicate the
> >>> execution of procedures of this kind. But soon I found
> >>> issue raised -- all the SPs have "select into..."
> would >>> be failed while replicating, the error is all
> like >>> "Message from server: Message: 226, State 1,
> Severity 16 >>> -- 'SELECT INTO command not allowed within
> >>> multi-statement transaction." I think it's due to
> >>> the RS will pack the procedure execution with
> >>> transaction automatically. but this prevent the
> "select >>> into" inside these procedures -- even only
> select into a >>> temp table. As we all know that the
> "select into" a temp >>> table is very common in T-SQL
> programming. I can't >>> remove this from all the
> procedures. I have totally >>> no idea on this issue now.
> Would anyone kind to help? >> Thanks.>
> >>> Best Regards
> >>> Eisen


"Mark A. Parsons" <iron_horse Posted on 2011-12-22 21:09:47.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: How to replicate a SP has "select into" inside?
References: <4ef33004$1@forums-1-dub> <4ef33222.640e.1681692777@sybase.com>
In-Reply-To: <4ef33222.640e.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: <4ef39c9b$1@forums-1-dub>
Date: 22 Dec 2011 13:09:47 -0800
X-Trace: forums-1-dub 1324588187 10.22.241.152 (22 Dec 2011 13:09:47 -0800)
X-Original-Trace: 22 Dec 2011 13:09:47 -0800, vip152.sybase.com
Lines: 78
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.rep-server:9120
Article PK: 870283

See the following manuals for more details:

RS 12.6, New Features Guide ("SQL statement replication")

ASE 15.0.3, New Features Gide ("SQL Statement Replication")

On 12/22/2011 08:35, Eisen wrote:
> Hi Mark
> Thanks. Would you please tell me a little more about SQL
> statement replication? I don't know it before. Thanks.
> Best Regards
> Eisen
>
>> Sorry, I misspoke in my earlier email ... proc replication
>> isn't going to work for select/into.
>>
>> If you're running ASE 15.0.3+ and RS 15.2+ you might try
>> SQL statement replication, though you'll also need to
>> change your stored procs (eg, SQL statement replication
>> doesn't work with cross-database references).
>>
>> On 12/22/2011 00:04, Eisen wrote:
>>> Hi Mark
>>> Thanks a lot for your help. Anyway I thought it
>>> before, but in practice, I can't do that, for most of
>>> these "select into..." were used in dynamic SQL to
>>> create temp tables with different structure -- eg.
>>> declare @cmd='select * into
>>> '+db_name(tempdb_id())+'..tmptb_'+newid()+' from
>>> '+@tbname execute(@cmd)
>>> ...
>>> As each time, the @tbname is different, so I can't
>>> create table before, and I can only use "select into" to
>>> create this table.
>>> Is there any other solution? Thanks
>>> ps: I'm thinking-- if I use xp_cmdshell inside
>>> transaction to create a new session maybe it can solve
>>> this but it's also another problem on security for the
>>> executor will not be the real executor of this
>> procedure...>
>>> Best Regards
>>> Eisen
>>>
>>>> Assuming all of your select/into commands are for
>> creating>> #temp tables (eg, you're not using select/into
>> to create>> permanent tables in non-temporary databases)
>> ..>>
>>>> Create a new user-defined temporary database on the RDS
>>>> and configure it with 'ddl in tran' enabled.
>>>>
>>>> Bind your DSI's maintuser login to this new
>> user-defined>> temporary database.
>>>>
>>>> As long as the maintuser login is the only session
>> using>> the user-defined temporary database there should
>> be no>> issues with excessive blocking on the system
>> tables.>>
>>>>
>>>> On 12/21/2011 22:39, Eisen wrote:
>>>>> Dear all
>>>>> Since the RS can't replicate the DDL statements
>>>>> inside procedure in MSA, I have to replicate the
>>>>> execution of procedures of this kind. But soon I found
>>>>> issue raised -- all the SPs have "select into..."
>> would>>> be failed while replicating, the error is all
>> like>>> "Message from server: Message: 226, State 1,
>> Severity 16>>> -- 'SELECT INTO command not allowed within
>>>>> multi-statement transaction." I think it's due to
>>>>> the RS will pack the procedure execution with
>>>>> transaction automatically. but this prevent the
>> "select>>> into" inside these procedures -- even only
>> select into a>>> temp table. As we all know that the
>> "select into" a temp>>> table is very common in T-SQL
>> programming. I can't>>> remove this from all the
>> procedures. I have totally>>> no idea on this issue now.
>> Would anyone kind to help?>> Thanks.>
>>>>> Best Regards
>>>>> Eisen


Eisen Posted on 2011-12-23 06:06:18.0Z
Sender: 1eb5.4eefed91.1804289383@sybase.com
From: Eisen
Newsgroups: sybase.public.rep-server
Subject: Re: How to replicate a SP has "select into" inside?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4ef41a5a.18a3.1681692777@sybase.com>
References: <4ef39c9b$1@forums-1-dub>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 22 Dec 2011 22:06:18 -0800
X-Trace: forums-1-dub 1324620378 172.20.134.41 (22 Dec 2011 22:06:18 -0800)
X-Original-Trace: 22 Dec 2011 22:06:18 -0800, 172.20.134.41
Lines: 91
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.rep-server:9121
Article PK: 870284

Hi Mark
Thanks. Oh. It's the SQLDML replication... Yes. I already
enabled it and just I enabled it, so the the issue of
"select into" in transaction would raised... :-)
Now I wrote a procedure to copy table by reading system
informations from system tables to replace "select into"...
Also it looks slow and stupid but maybe it's the only
solution for my issues.
Anyway, thanks a lot for your patience and help.

Best Regards
Eisen

> See the following manuals for more details:
>
> RS 12.6, New Features Guide ("SQL statement replication")
>
> ASE 15.0.3, New Features Gide ("SQL Statement
> Replication")
>
> On 12/22/2011 08:35, Eisen wrote:
> > Hi Mark
> > Thanks. Would you please tell me a little more about
> > SQL statement replication? I don't know it before.
> > Thanks. Best Regards
> > Eisen
> >
> >> Sorry, I misspoke in my earlier email ... proc
> replication >> isn't going to work for select/into.
> >>
> >> If you're running ASE 15.0.3+ and RS 15.2+ you might
> try >> SQL statement replication, though you'll also need
> to >> change your stored procs (eg, SQL statement
> replication >> doesn't work with cross-database
> references). >>
> >> On 12/22/2011 00:04, Eisen wrote:
> >>> Hi Mark
> >>> Thanks a lot for your help. Anyway I thought it
> >>> before, but in practice, I can't do that, for most of
> >>> these "select into..." were used in dynamic SQL to
> >>> create temp tables with different structure -- eg.
> >>> declare @cmd='select * into
> >>> '+db_name(tempdb_id())+'..tmptb_'+newid()+' from
> >>> '+@tbname execute(@cmd)
> >>> ...
> >>> As each time, the @tbname is different, so I can't
> >>> create table before, and I can only use "select into"
> to >>> create this table.
> >>> Is there any other solution? Thanks
> >>> ps: I'm thinking-- if I use xp_cmdshell inside
> >>> transaction to create a new session maybe it can solve
> >>> this but it's also another problem on security for the
> >>> executor will not be the real executor of this
> >> procedure...>
> >>> Best Regards
> >>> Eisen
> >>>
> >>>> Assuming all of your select/into commands are for
> >> creating>> #temp tables (eg, you're not using
> select/into >> to create>> permanent tables in
> non-temporary databases) >> ..>>
> >>>> Create a new user-defined temporary database on the
> RDS >>>> and configure it with 'ddl in tran' enabled.
> >>>>
> >>>> Bind your DSI's maintuser login to this new
> >> user-defined>> temporary database.
> >>>>
> >>>> As long as the maintuser login is the only session
> >> using>> the user-defined temporary database there
> should >> be no>> issues with excessive blocking on the
> system >> tables.>>
> >>>>
> >>>> On 12/21/2011 22:39, Eisen wrote:
> >>>>> Dear all
> >>>>> Since the RS can't replicate the DDL
> statements >>>>> inside procedure in MSA, I have to
> replicate the >>>>> execution of procedures of this kind.
> But soon I found >>>>> issue raised -- all the SPs have
> "select into..." >> would>>> be failed while replicating,
> the error is all >> like>>> "Message from server:
> Message: 226, State 1, >> Severity 16>>> -- 'SELECT INTO
> command not allowed within >>>>> multi-statement
> transaction." I think it's due to >>>>> the RS will pack
> the procedure execution with >>>>> transaction
> automatically. but this prevent the >> "select>>> into"
> inside these procedures -- even only >> select into a>>>
> temp table. As we all know that the >> "select into" a
> temp>>> table is very common in T-SQL >> programming. I
> can't>>> remove this from all the >> procedures. I
> have totally>>> no idea on this issue now. >> Would
> anyone kind to help?>> Thanks.> >>>>> Best Regards
> >>>>> Eisen