Transactions with sqlserver

[Matthias]

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.

[joel_bourgault]

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?

[Matthias]

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.

[rriley99]

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