Hi David

I did try several times your demonstration but I couldn't get the stagingCdcContract Table populate with data.

When I run the stored procedure "Exec [dbo].[sPgGetCDCContract] @FromLSNDate=?, @ToLSNDate=?" I get an empty table in return, So I don't know whether I have done something wrong or the stored procedure has an mistake.

Here is the [sPgGetCDCContract] code:

USE [CDCDemo]

GO

/****** Object: StoredProcedure [dbo].[sPgGetCDCContract] Script Date: 11/12/2011 15:43:18 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[sPgGetCDCContract]

@FromLSNDate as datetime,

@ToLSNDate as datetime

AS

begin

SET NOCOUNT ON

SET ROWCOUNT 0

declare @from_lsn binary(10)

declare @to_lsn binary(10)

SET @from_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than', @FromLSNDate);

SET @to_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal', @ToLSNDate);

if (@from_lsn is not null and @to_lsn is not null and @from_lsn < @to_lsn)

begin

DECLARE @name_ordinal int

DECLARE @year_ordinal int

DECLARE @length_ordinal int

DECLARE @type_ordinal int

DECLARE @amount_ordinal int

DECLARE @last_change_id_ordinal int

SET @name_ordinal = sys.fn_cdc_get_column_ordinal('dbo_contract','name')

SET @year_ordinal = sys.fn_cdc_get_column_ordinal('dbo_contract','year')

SET @length_ordinal = sys.fn_cdc_get_column_ordinal('dbo_contract','length')

SET @type_ordinal = sys.fn_cdc_get_column_ordinal('dbo_contract','type')

SET @amount_ordinal = sys.fn_cdc_get_column_ordinal('dbo_contract','amount')

SET @last_change_id_ordinal = sys.fn_cdc_get_column_ordinal('dbo_contract','last_change_id')

SELECT cdc.fn_cdc_get_all_changes_dbo_contract.*,

sys.fn_cdc_map_lsn_to_time(__$start_lsn) as 'last_change_date',

cast(isnull(user_name, '') as varchar(100)) as 'last_change_name',

sys.fn_cdc_is_bit_set(@name_ordinal,__$update_mask) as 'IsNameUpdated',

sys.fn_cdc_is_bit_set(@year_ordinal,__$update_mask) as 'IsYearUpdated',

sys.fn_cdc_is_bit_set(@length_ordinal,__$update_mask) as 'IsLengthUpdated',

sys.fn_cdc_is_bit_set(@type_ordinal,__$update_mask) as 'IsTypeUpdated',

sys.fn_cdc_is_bit_set(@amount_ordinal,__$update_mask) as 'IsAmountUpdated',

sys.fn_cdc_is_bit_set(@last_change_id_ordinal,__$update_mask) as 'IsLastChangeIdUpdated'

FROM cdc.fn_cdc_get_all_changes_dbo_contract( @from_lsn, @to_lsn, 'all')

left join Users

on Users.user_id = fn_cdc_get_all_changes_dbo_contract.last_change_id

end

else

begin

-- return empty row

select cdc.dbo_contract_CT.*,

getdate() as 'last_change_date',

'' as 'last_change_name',

cast(0 as bit) as 'IsNameUpdated',

cast(0 as bit) as 'IsYearUpdated',

cast(0 as bit) as 'IsLengthUpdated',

cast(0 as bit) as 'IsTypeUpdated',

cast(0 as bit) as 'IsAmountUpdated',

cast(0 as bit) as 'IsLastChangeIdUpdated'

from cdc.dbo_contract_CT

where __$start_lsn = 0x00000000000000000000

end

end

GO

Thanks