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
