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

[gadeyne.bram]

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?

[shawkins]

> I've set the SQL Connection in my Java project with setAutoCommit(false)

You need to have autoCommit true.  AutoCommit false will start a local transaction.

> als tried adding a "SET AutoCommitTxn false"

You mean from the client correct? It should give you an exception when the value is not as expected.  You should use SET AutoCommitTxn OFF, or set the value in your connection string. 

[gadeyne.bram]

Yes, I had SET AutoCommitTxn OFF but leaving conn.setAutoCommit to true solved the problem.

Thanks!