Wait until an operation finished
-
Hi Friends, I have a database on which I have enabled CDC on some tables. As CDC captures auditing data on transactions basis, I am accessing that data and putting in flat file by using a Store Procedure Now is there any way in T-SQL so that, we can wait until a transaction finishes to capture the data. Because if I capture the information half way we may miss some of the data.
Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."
-
Hi Friends, I have a database on which I have enabled CDC on some tables. As CDC captures auditing data on transactions basis, I am accessing that data and putting in flat file by using a Store Procedure Now is there any way in T-SQL so that, we can wait until a transaction finishes to capture the data. Because if I capture the information half way we may miss some of the data.
Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."
Can't you use AFTER INSERT , AFTER UPDATE T-SQL (triggers) in this scenario? Am I missing something that you are not explaining in detail?
-
Can't you use AFTER INSERT , AFTER UPDATE T-SQL (triggers) in this scenario? Am I missing something that you are not explaining in detail?
Sure, a trigger works on the Table level, where as I want it on the transaction level, on transaction may be using multiple tables. The other interesting and more complex thing is Change Data Capture works on log file asynchronously to write the values into Audit tables. But luckily CDC writes information as Transaction based, like it gives same start_lsn id for same transaction, which I can use. But for example when I retrieving data from CDC Audit tables and putting it in the Flat tables then I want to wait if CDC is in between writing a Transaction on to the CDC Audit tables. Can I do it? Thanks in advance.
Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."
-
Sure, a trigger works on the Table level, where as I want it on the transaction level, on transaction may be using multiple tables. The other interesting and more complex thing is Change Data Capture works on log file asynchronously to write the values into Audit tables. But luckily CDC writes information as Transaction based, like it gives same start_lsn id for same transaction, which I can use. But for example when I retrieving data from CDC Audit tables and putting it in the Flat tables then I want to wait if CDC is in between writing a Transaction on to the CDC Audit tables. Can I do it? Thanks in advance.
Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."
My suggestion - give each 'transaction' a GUID. Create a table that holds the GUIDs of 'transactions' that have been completed. Write the relevant GUID into this table when a 'transaction' is completed. Then when it comes to determining whether the data is ready to be sent to flat files just look to see if the GUID exists in your completed 'transaction' GUID table.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
-
Sure, a trigger works on the Table level, where as I want it on the transaction level, on transaction may be using multiple tables. The other interesting and more complex thing is Change Data Capture works on log file asynchronously to write the values into Audit tables. But luckily CDC writes information as Transaction based, like it gives same start_lsn id for same transaction, which I can use. But for example when I retrieving data from CDC Audit tables and putting it in the Flat tables then I want to wait if CDC is in between writing a Transaction on to the CDC Audit tables. Can I do it? Thanks in advance.
Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."
May be this is not direct solution for you but should give you some idea. sql server - Delayed availability of historical data when using CDC - Database Administrators Stack Exchange[^]