Unable to execute stored procedure that is marked for replication explicitly.

[DilipVoora]

Hi Experts,

We are trying to replicate stored procedure(psp_process_dp) in a database(PDB for a MSA Replication to two targets/RDBs) explicitly by using the below to see a performance improvement.

Steps followed:

1. Executed sp_setrepproc psp_process_dp,'function' to mark the sp explicitly for replication.
2. Stored procedure has 'select into' involved so we had to set the repdbmode to 's' using sp_setrepdbmode.
3. Also the stored procedure has create statements for creatinf temprorary tables so we enabled 'ddl in tran' database option for the database that has the stored procedure.
4. Enabled 'deferred name resolution' parameter in PDB and RDBS and also at the connection level (for RDBs).

However while executing the stored procedure we are getting the below error but the execution of the stored procedure is successful when it is unmarked

Error:

1> declare @Nrc int
2> exec @Nrc = psp_process_dp '2023-03-16', 0, 2,100, 96, 'SA ', '2023-03-16',207, NULL, NULL
3> select @Nrc
4> go

-----------

0

(1 row affected)

Msg 226, Level 16, State 1:
Server 'ZDB', Procedure 'psp_process_dp', Line 331:
SELECT INTO command not allowed within multi-statement transaction.
(1 row affected)
Msg 208, Level 16, State 6:
Server 'ZDB', Procedure 'psp_process_dp', Line 706:
#dp_display_temp not found. Specify owner.objectname or use sp_help to check whether the object exists (sp_help may produce lots of output).

(return status = -6)

-----------

-6

Am I missing anything here to take care of? Please advise.

Details:

Operating system : AIX 7.2
ASE version : 15.7 SP135
Replication Server version : 15.7.1 SP306 ROLLUP

Regards,
Dilip Voora

[Mark_A_Parsons]

tl:dr

• select into can only be replicated via DML statement replication
• replicated stored procs cannot run the select into command (select into will need to be converted to create table + insert)
• replicated stored procs are automatically invoked in a transaction wrapper which means ...
• DDL commands will fail unless the database has been configured with the db option ddl in tran = true; (for create table the database - where the table is to be created - will need to have ddl in tran = true; for #temp tables this means any temporary database - where the #temp table could be created - will need to have ddl in tran = true)
• sp_setrepdbmode(s) and deferred name resolution should not have an impact in this scenario (re: stored proc replication)

-------------------------- more details ...

From the SAG Vol 2 Manual:

From the Reference Manual (sp_setrepproc):

[DilipVoora]

Hi Mark,

So, if the stored procedure is not marked for replication explicitly, the select into and create temporary tables in the stored procedure gets processed by referring to the replicate DDL clause at database rep def level and select into with sp_setrepdbmode to replicate. Is this is a correct understanding?

NOTE: I am asking this after looking at the statement in documentation you highlighted and just to understand the behaviour of the sp.

Regards,
Dilip Voora

[Mark_A_Parsons]

#temp table creations will not be replicated.

As to whether or not the select into will be replicated ... *shrug* ... yes, no, maybe ... not enough info to say one way or another (eg, select into a #temp table?); I suggest setting up a test environment and run through the various scenarios to see which ones (don't) work.