Using Incremental load logic with SQL 08 Change Data Capture (CDC) Feature

villegasi03 — Fri, 29 Jun 2012 01:13:14 GMT

QV Community,

We are upgrading our SQL server soon to the 08 edition. One of the databases I pull info from is on this server and its an operational DB. I've been told that read access will be cut off after the upgrade and that I must incorporate Change Data Capture (CDC) into my logic. Problem is that I know little about it. I have a QVW that is solely used to create my QVDs and I am using some incremental load logic.

I am hoping that someone could offer their expertise on SQL functionality so that I may be able to incorperate it into my existing logic. Below are some specifics:

On my initial load I direct QV to a back up of the operational DB which will serve as my base QVD data and on every subsequent load new data will be available only through the CDC tables.
Due to security issues I will have to have QV execute a stored procedure which will load the CDC data in staging tables. (all fields are a mirror image of the original tables except for fields generated by the CDC)
I have to incorporate the LSN (Logical Sequence Number) into my logic
I have to use the values in the __$operation field to deal with inserts, updates and deletes (All of my tables are updates and inserts only). _$operationcolumn values are: 1 = delete, 2 = insert, 3 = update (values before update) which I am ignore and 4=update (values after update).

From what I have read the LSN is a binary number but I am not sure how to get the MAX to use in logic. SQL has a function for it but dont know how to use it in QV. I've attached example of how I am dealing inserts and updatable tables currently. The stored procedure I was given and need to be executing is:

SQL execute [DBA].[CDC_Read] '2012-06-12', '2012-06-13', 'ActivityLog';

But I think I should replace the dates with QV variables but not sure how to do this yet.

Maybe something like: SQL EXECUTE [DBA].[CDC_Read] '$(vCDCStart)', '$(vCDCEnd)', 'ActivityLog';

I was given this link to see example of the SQL execution script to get ideas: http://www.codeproject.com/Articles/166250/Microsoft-SQL-Server-2008-Change-Data-Capture-CDC

topic Using Incremental load logic with SQL 08 Change Data Capture (CDC) Feature in QlikView

Using Incremental load logic with SQL 08 Change Data Capture (CDC) Feature