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
select * into tab2 from tab1
I have created database replication definition using following command :
create database replication definition def1
with primary at dewdfgwp01694.src
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
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.