How to enable multi-statement replication like select into in SAP Replication server

[Former Member]

Hi All,

Currently I am worling on replication of non logged operation using SAP Replication Server.My source and target databases both are Sybase ASE 15.7. I created a normal stored procedure having non logged operation like :

create procedure proc1

as

select * into tab2 from tab1

I have created database replication definition using following command :

create database replication definition def1

with primary at dewdfgwp01694.src

replicate DDL

replicate functions

replicate transactions

replicate tables

and created subscription as well

After marking the procedure using sp_setrepproc proc1,'function', I started the repagent (sp_start_rep_agent src)

But after marking the procedure I am unable to execute the procedure and having the error :

SELECT INTO command not allowed within multi statement transactions

Sybase error code=226

Can anyone please guide me in this situation

FYI : I have executed all three commands in primary database :

sp_dboption src,'select into/bulkcopy/pllsort',true;

sp_dboption src,'ddl in tran',true;

sp_dboption src,'full logging for all',true

[Former Member]

I am getting the error in primary database(Sybase ASE console) as well as in repserver .

This error is occurring after the marking of the procedure in the primary database for replicating.

And after getting this error i am unable to replicate any other table or procedure(seems the DSI thread is going down in repserver)

the error in repserver is given below :

T. 2014/09/20 16:58:03. (27): Last command(s) to 'server_name.trg':

T. 2014/09/20 16:58:03. (27): 'begin transaction  [0a] exec proc1  '

E. 2014/09/20 16:58:03. ERROR #1028 DSI EXEC(103(1) 'server_name.trg) - dsiqmint.c(4710)

Message from server: Message: 226, State 1, Severity 16 -- 'SELECT INTO command not allowed within multi-statement transaction.

'.

H. 2014/09/20 16:58:03. THREAD FATAL ERROR #5049 DSI EXEC(103(1) server_name.trg) - dsiqmint.c(4723)

The DSI thread for database 'server_name.trg' is being shutdown. DSI received data server error #226 which is mapped to STOP_REPLICATION. See logged data server errors for more information. The data server error was caused by output command #0 mapped from input command #0 of the failed transaction.

I. 2014/09/20 16:58:03. The DSI thread for database 'server_name.trg' is shutdown.

I. 2014/09/20 18:07:48. Replication Agent for server_name.src connected in passthru mode.

[Former Member]

I executed those three sp_dboption parameter in replicate database as well..!!!! Still i am getting the same error.

Except from the above three parameters in sp_dboption what other parameters should i try???

[former_member182090]

It is not possible to execute SELECT-INTO inside a transaction in ASE. This is unrelated to Replication Server.  The "ddl in tran" DB option does not change that.

So, when you get this error in your primary database, it means that there is an active transaction at the moment when the procedure is called. You need to find out why/how (sp_transactions) and adjust your code so that this no longer happens - otherwise it will always raise an error and there is no workaround. Again, this has nothing to do with replication.

(NB: feature request #765273 will address this issue but that is not yet available so it won't help you right now)

On a side note, when replicating DDL, you should enable the RepAgent option 'send warm standby xacts' in the primary DB to avoid the DDL being executed inside a transaction on the replicate side.

But that won't address the problem above, as explained.

[Former Member]

Thanks for your reply Rob!!!!

Does select into inside a procedure related to active transaction???

The procedure is executing perfectly fine before marking the procedure for replication.

After the marking only the procedure is unable to replicate.