Showing results for 
Search instead for 
Did you mean: 

Transactions with sqlserver

New Contributor III

Hi Community,

Is there a possibility to have cross-snap transactions in snaplogic, where we manage the transactions manually (e.g. by manually issuing commit or rollback at the end of the transaction)?

for a usecase, we’d need to run multiple inserts into different tables in a SQL Server database in one transaction (if one of the queries fails, we’ll need to roll back all inserts).

it’ll need to be transaction-safe, as there is an application running on top of the tables which does similar inserts.
Obviously this could be done with the SQL execute snap - however that’d mean coding the whole SQL query manually (including potential SQLi escaping) - which we’d not really like to do, for hopefully obvious reasons.


New Contributor II

Hello, same question on my side, where multiple inserts occur within several MySQL snaps (so not the same server, but same need).

But maybe it is sufficient to force “autocommit=False” in these MySQL snaps: if I understand correctly, the commit will be performed only when the pipeline completes, i.e. no commit happens if the pipeline fails.

Is this understanding correct? Is this a correct way to handle this design?

New Contributor III

This might be true if you’re inserting several records into one table
but for my usecase, i’ve been looking at inserts into different tables (e.g. a customer table, and a customer contact table) - which should be combined to fail together, which currently seems impossible without the SQL execute snap.

New Contributor III

It would be nice to have a error pipeline rollback or something like that. Did y'all find a solution?