2 Replies Latest reply on Jun 1, 2018 9:40 AM by gadeyne.bram

    Approach to copy data from one non-xa datasource to another non-xa datasource

    gadeyne.bram Master

      Hi,

       

      I have 2 sql server instances. I need to copy data over from one instance (kd_matric) to the other (tlmdb). XA transactions will not be supported on either of the SQL Server instances.

       

      I have written a procedure in my DDL based VDB:

       

      This was the first version:

       

      CREATE VIRTUAL PROCEDURE CopyPatientInfoFromKDMatricToTLMDB1() AS 
      BEGIN
            /* Copy PatientData */
            insert into tlmdb.PatientData(GUID,Short_Birthdate,IsNew)
            select pd.GUID,pd.Short_Birthdate,pd.IsNew
            from kd_matric.PatientData pd; 
      END

       

      But this throws "ARJUNA012140: Adding multiple last resources is disallowed".

       

      I then thought that maybe I could first insert the records in an in memory table and then insert them from the temp table to the second source.

       

      CREATE VIRTUAL PROCEDURE CopyPatientInfoFromKDMatricToTLMDB() AS
      BEGIN
            /* Copy PatientData */
            insert into #tmp_data_to_add1
            select pd.GUID,pd.Short_Birthdate,pd.IsNew
            from kd_matric.PatientData pd;
      
            insert into tlmdb.PatientData(GUID,Short_Birthdate,IsNew)
            select * from #tmp_data_to_add1;
      
            drop table #tmp_data_to_add1; 
      END
      
      

       

      But this also throws the same error.

       

      I've set the SQL Connection in my Java project with setAutoCommit(false) and als tried adding a "SET AutoCommitTxn false" but they all result in the same issue.

       

      Is there some other approach that I can try?