cftransaction with multiple data sources

I have a script that basically pulls data from one dsn and inserts it using another dsn.

I am currently wrapping each insert in DSN 1 with a cftry and then at the end

  <cfcatch type = "DATABASE">
    <cfset commitIt = "No">
    <cftransaction action = "rollback"/>

at the end of the entire transaction i have the following:

  <cfif commitIt>
    <cftransaction action = "commit"/>
    Commits the pending insertion.
    <cfset commitIt = "Yes">


I'm getting an error  of:

 Data source eventreg verification failed.
The root cause was that: java.sql.SQLException: Datasource names for all the database tags within CFTRANSACTION must be the same.

This is correct because i have nested queries inside the transaction that lookup records and verify them.

What solution can i do to safely complete this transaction even when i have queries like this.
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

PluckaConnect With a Mentor Commented:

You simply can't use cftransaction across datasources.

Put the two different tasks within two seperate <cftransaction blocks.

If one fails, you may need to code manually a rollback.

All Courses

From novice to tech pro — start learning today.