You are viewing limited content. For full access, please sign in.

Question

Question

SQL Transactions in Workflow

Workflow
Updated February 3, 2021
asked on February 3, 2021

Is it possible to use SQL Transactions within a workflow?

It appears that the database connection is reset with each SQL call, so we are unable to issue a COMMIT or ROLLBACK as they are not valid.

Background:

We are trying to update some tables and would like to be able to delete all of the records and then insert the new ones as they can change frequently. If there is a problem, we want to rollback and not delete the old records.

Perhaps there is some way to indicate the database connection should be held?

0 0

Answer

SELECTED ANSWER
replied on February 3, 2021

If you're asking if you can have a transaction across multiple runs of the data activities in Workflow, then no. You can use a Custom Query to write your own transaction, but then you have to insert all rows in that one activity.

2 0
replied on February 3, 2021

So to verify, in a TRY/CATCH, you cannot have multiple steps:

Try:

- Query Source documents (Query Data, Data Source "A")

- Begin Transaction (Custom Query , Data Source "B")

- Delete Destination Documents  (Custom Query, Data Source "B")

- Insert Documents from Source (Insert Data, Data Source "B")

- Commit Transaction (Custom Query, Data Source "B")

Catch:

- Rollback Transaction (Custom Query, Data Source "B")

 

Thanks for confirming!

0 0
replied on February 3, 2021

Correct. Anything you do in Custom Query/Insert Data is committed when the activity completes.

1 0

Replies

You are not allowed to reply in this post.
You are not allowed to follow up in this post.

Sign in to reply to this post.

Details
Related Posts
Loading ...