idea Re: Capture XACT ID transaction ID during MS SQL Replication in Suggest an Idea

Capture XACT ID transaction ID during MS SQL Replication

--Brad-- — Fri, 29 Apr 2022 16:13:05 GMT

When capturing the header information from the MS SQL transaction log, currently captures the field [Transaction ID] from the physical LDF TLOG file. There is another transaction id, however, called [XACT ID] in the TLOG file. The [XACT ID] is the transaction id that is available during the MS SQL transaction itself, and would be very valuable for mapping other events to the change event in .

In our use case, we capture the user logged into the application at the time of the change for auditing purposes. We log these using MS SQL's native SQL Audit. We need some common ID that can be captured during the SQL Audit that can be used to map to the change that is captured in . The transaction_id field that is available at the time of the SQL Audit is the [XACT ID] field in the LDF TLOG file. If just added this field to what they capture during CDC, then we could more easily join this data at a future time when we need to report out for an audit.

Here's a screenshot from MS SQL comparing the SQL Audit data (top) with the LDF TLOG file (bottom). Note: the [Transaction ID] value in the bottom set is used as the transaction__id in Qlik, though the bytes are reversed. (0000:113c53e1 becomes E1533C1100000000)

Re: Capture XACT ID transaction ID during MS SQL Replication

patrickgallucci — Tue, 08 Nov 2022 21:06:51 GMT

Any update on this. I am having a hard time believing no one else has this issue. If not, there is a big untapped market for compliance audit that Qlik could go after....if this was resolved. I have a brut force way to solve this, but itwould be nice if Qlik could just add XACT_ID as a variable like the lsn. Thanks.

Re: Capture XACT ID transaction ID during MS SQL Replication

john_wang — Tue, 15 Nov 2022 02:37:28 GMT

Hello,

Per our investigation in the past:

https://community.qlik.com/t5/Qlik-Replicate/How-to-get-the-MSSQL-Transaction-ID/td-p/1886275

we need PM team input for this feature.

thanks,

John.

Re: Capture XACT ID transaction ID during MS SQL Replication - Status changed to: Open - On Roadmap

Nulee_Massaro — Wed, 08 Feb 2023 19:23:27 GMT

Thank you for your suggestion. We have added this feature to our roadmap!

From now on, please track this idea from the Ideation por...

Meghann_MacDonald — Wed, 02 Aug 2023 15:09:53 GMT

From now on, please track this idea from the Ideation portal.

Link to new idea

Meghann

NOTE: Upon clicking this link 2 tabs may open - please feel free to close the one with a login page. If you only see 1 tab with the login page, please try clicking this link first: Authenticate me! then try the link above again. Ensure pop-up blocker is off.

Re: Capture XACT ID transaction ID during MS SQL Replication - Status changed to: Closed - Archived

Ideation — Wed, 02 Aug 2023 15:09:54 GMT