Warm standby and SP with temp tables

[jmtorres]

Hello,

I have  Warm Stand By which is working fine within a DB pair in PDs and the RDS. Versions : ASE 15.5 ->RS 15.7SP200->ASE 15.5, Linux

I'm looking for the simplest/best way to code a SP that creates and drops temporary table in its code ,and successfully replicates to the RDS. Currently in the PDS I have something like this :

Create Procedure dbo.Man2 As

Begin

    CREATE TABLE priv_db.dbo.tDLDetDet_man

    (

        lIdtplDLDetDet       numeric(10,0),

        lIdtplDeudorLeasing  numeric(10,0) NOT NULL,

        cTipoDeuda           char(4)       NOT NULL,

        cPeriodo             char(6)       NOT NULL,

        nCodInstituc         smallint      NOT NULL,

        cGlosaInstituc       char(40)      NOT NULL,

        nCorrelativoRegistro smallint      NOT NULL

    )

    insert priv_db.dbo.tDLDetDet_man

    select machi..tDLDetDet.lIdtplDLDetDet, machi..tDLDetDet.lIdtplDeudorLeasing, machi..tDLDetDet.cTipoDeuda, machi..tDLDetDet.cPeriodo, machi..tDLDetDet.nCodInstituc, machi..tDLDetDet.cGlosaInstituc, machi..tDLDetDet.nCorrelativoRegistro

    from machi..tDLDetDet

    Create Table #temp(

        xProRut             char(12) not null,

        ProRazonSocial    char(60) not null,

        ProNomFantasia  int not null

    )

    insert #temp

    select "sdvsd", "sdfgvsdfg", 1

    insert #temp

    select "sdvsd", "sdfgvsdfg", 2

    --select #temp.xProRut, #temp.ProRazonSocial, #temp.ProNomFantasia from #temp

    drop table #temp

End

go

But I'm getting this message :

Message from server: Message: 277, State 1, Severity 16 -- 'There was a transaction active when exiting the stored procedure 'Man2'. The temporary table '#temp' was dropped in this transaction either explicitly or implicitly. This transaction has been aborted to prevent data.

1. I. 2015/06/26 17:18:54. Message from server: Message: 266, State 1, Severity 10 -- 'Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRAN is missing. Previous count = 1, Current count = 0.

So , could creating the #temp table outside the SP should solve this issue or should the SP be re-coded in a way that invokes another SP that creates the #temp table ?

Thank you

Regards

Jose-Miguel

[jmtorres]

Hello,

The only way that we get this to work was using a physical table instead of the of a temporary.

Not sure if it's the best solution though

Regards

JMT

[former_member89972]

If the procedures are not changed too frequently

and only for those that need temp tables created

How about following sequence :

Session on primary database

- turn off  replication

- run the SQL to create/catalog  stored procedure

Session on standby database

- run the SQL to create/catalog stored procedure

HTH

Avinash

[jmtorres]

Avinash,

Thank you. We thought about your idea but unfortunately due to adminstartation restrictions, we can't modify directly the secondary  site( replicated).

Regards

Jose