I am using SQL Server 2016 with a linked server to ADABAS using the CONNX 11 SP3 driver.
I would like to start a transaction, run one to many transactions, and then commit them at the end of the process, but if any of the “EXEC … AT …” fail, then I need to rollback everything that it did. Is this possible?
Sample code:
DECLARE @SQLCmd varchar(500), @Begin bit = 1, @Update1 bit = 1, @Update2 bit = 1, @Commit bit = 0, @Rollback bit = 1;
SET @SQLCmd = ‘Begin Tranrasaction’
IF @Begin = 1
EXEC(@SQLCmd) AT [ADABASLinkedServerName]
SET @SQLCmd = ‘Update TableA Set Col1 = ‘‘F’’ WHERE ISN_FILE_1 = 1’
IF @Update1 = 1
EXEC(@SQLCmd) AT [ADABASLinkedServerName]
SET @SQLCmd = ‘Update TableB Set Col2 = ‘‘F’’ WHERE ISN_FILE_1 = 1’
IF @Update2 = 1
EXEC(@SQLCmd) AT [ADABASLinkedServerName]
SET @SQLCmd = ‘Commit Tran’
IF @Commit = 1
EXEC(@SQLCmd) AT [ADABASLinkedServerName]
SET @SQLCmd = ‘Rollback’
IF @Rollback = 1
EXEC(@SQLCmd) AT [ADABASLinkedServerName]
First, I apologize for the late response to this inquiry, my forum notification settings somehow changed and I was no longer alerted of any new posts.
In order to roll back a transaction in this example, several things must be considered. All of the databases in your CONNX data dictionary must be transaction capable.
If all of your databases in the CDD are from Adabas, then this is already true.
SQL server will enlist 3rd party drivers into a two phase commit transaction (XA for short).
This will require, at a minimum, CONNX 11.5. However I highly recommend you upgrade to the latest version - 13.8, to accomplish this task.
You must enable transaction support in the linked server properties, and you must use the CONNX OLEDB Provider as the driver type, as this as the required XA support for distributed transactions.
After you have performed the following steps - you can simply using the normal SQL server mechanisms to begin, commit and rollback transactions, and SQL server takes care of the rest and will issue the XA commit and XA rollback as required.
Since there are quite a few steps involved here, I recommend you open a support ticked, and our support team can help guide you through this process.