Edit

sys.fn_cdc_decrement_lsn (Transact-SQL)

  • Article

Applies to: SQL Server

Returns the previous log sequence number (LSN) in the sequence based upon the specified LSN.

Transact-SQL syntax conventions

Syntax

  
sys.fn_cdc_decrement_lsn ( lsn_value )

Arguments

lsn_value

LSN value. lsn_value is binary(10).

Return Type

binary(10)

Remarks

The LSN returned by the function is always less than the specified value, and no LSN values can exist between the two values.

Permissions

Requires membership in the public database role.

Examples

The following example uses sys.fn_cdc_decrement_lsn to set the upper LSN boundary in a query that returns change data rows that have LSN values less than the maximum LSN value.

Use AdventureWorks2022;  
GO  
DECLARE @from_lsn binary(10), @to_lsn binary(10);  
SET @from_lsn = sys.fn_cdc_get_min_lsn('HumanResources_Employee');  
SET @to_lsn = sys.fn_cdc_decrement_lsn(sys.fn_cdc_get_max_lsn());  
SELECT * FROM cdc.fn_cdc_get_all_changes_HumanResources_Employee( @from_lsn, @to_lsn, 'all');   
GO

Note

Error 313 is expected if LSN range supplied is not appropriate when calling cdc.fn_cdc_get_all_changes_<capture_instance> or cdc.fn_cdc_get_net_changes_<capture_instance>. If the lsn_value parameter is beyond the time of lowest LSN or highest LSN, then execution of these functions will return in error 313: Msg 313, Level 16, State 3, Line 1 An insufficient number of arguments were supplied for the procedure or function. This error should be handled by the developer. Sample T-SQL for a workaround can be found at ReplTalk on GitHub.

See Also