April 24, 2013 at 3:25 am
Got a feeling this is something to do with an implied cast that I dont understand
declare @DataReady INT
SELECT @DataReady = 1
if not exists (select start_lsn from cdc.lsn_time_mapping where start_lsn <= 0X0002696800000AE90002 ) select @DataReady = 0
if not exists (select start_lsn from cdc.lsn_time_mapping where start_lsn >= 0X0002696E000002EE0001 ) select @DataReady = 2
select @DataReady as PkgLSNsValidated
declare @start_lsn binary(10),
@end_lsn binary(10)
declare @start_lsn_str nvarchar(42), @end_lsn_str nvarchar(42)
declare @DataReady1 int
set @start_lsn_str= 0X0002696800000AE90002
set @end_lsn_str= 0X0002696E000002EE0001
set @start_lsn = sys.fn_cdc_hexstrtobin(@start_lsn_str)
set @end_lsn = sys.fn_cdc_hexstrtobin(@end_lsn_str)
select @DataReady1 = 1
if not exists (select start_lsn from cdc.lsn_time_mapping where start_lsn <= @start_lsn_str) select @DataReady1 = 0
if not exists (select start_lsn from cdc.lsn_time_mapping where start_lsn >= @end_lsn_str) select @DataReady1 = 2
select @DataReady1 as PkgLSNsValidated
The first set of SQL returns 0 implying that the value is not valid
The second set of SQL returns 1 implying that the value IS valid
The second set has to accept the LSNs as string values (using SSIS)
Whats going on? Which is right and why?
Many thanks
M
April 24, 2013 at 3:41 am
Does the same thing happen if you put your hex values in quotes in the first query?
John
April 24, 2013 at 3:54 am
yes.
Also unless I am mistaken there is the two lines
set @start_lsn = sys.fn_cdc_hexstrtobin(@start_lsn_str)
set @end_lsn = sys.fn_cdc_hexstrtobin(@end_lsn_str)
that are not utilised.
I have repeatedly altered the code to get it to work so must have avoided those two lines. Wish I left notes in my code 🙁
Thanks
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply