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:
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