July 8, 2016 at 1:01 pm
Hi there,
I am enabling CDC on some "catalog" data within my database. We need to track changes to ALL data fields in our table and then be able to display it in a lovely fashion to the users on the screen to show change history by data element.
So, I know I get a lovely operation code in the CDC table and if I pull all #4 records I know SOMETHING changed on each #4 record. However, my problem is.... lets say a user changed column B 2 times. I will have 2 #4 records which would show me what each change was for column B. However, I write a query that says show me all the changes to column A. Nothing changed at all in column A when the two changes for column B were done. But, if I pull all the #4 records for column A it gives me their original values where in reality, nothing has changed for that particular field.
Does anyone have a script already done that you have used to parse out the CDC table records like this? It is super great for viewing just as a DBA and I know just by looking at stuff what has changed and what hasn't...but I need a way to display it to users in a nice pretty fashion.
Am I going to have to build in logic to my stored procedure to check to see if the #4 operation record value is identical to the #3 operation record value and therefore nothing has changed? I'm pretty sure I do... but it sounds complicated. 🙁
July 8, 2016 at 1:47 pm
You can also use __$update_mask column with sys.fn_cdc_is_bit_set CDC function to find columns which were updated for a particular record.
July 9, 2016 at 10:14 am
every time I try to use the darned built in cdc functions it keeps telling me insufficient number of arguments were supplied for the procedure when I know darned well it has 3. I'm so confused why it won't work.
July 9, 2016 at 10:28 am
Oh.... I figured it out... I didn't put dbo_ before my table name when I was getting the min lsn value. That's why it didn't work.
Thank you! I think I am on the right track now!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply