Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_cdc_lsn_time_mapping_procs

  No additional text.


Syntax
create procedure [sys].[sp_cdc_lsn_time_mapping_procs]
(
	@action nvarchar(16)
)
as
begin
	declare @stmt nvarchar(max)
		
    set nocount on

	-- Create/Upgrade stored procedure to populate cdc.lsn_time_mapping table
	set @stmt =
	N'
	
	-- Name: [cdc].[sp_ins_lsn_time_mapping]
	
	-- Description:
	--	Stored procedure used internally to populate cdc.lsn_time_mapping table
	
	-- Parameters:
	--	@start_lsn				binary(10)			-- Commit lsn associated with change table entry
	--	@tran_begin_time		datetime			-- Transaction begin time of entry
	--	@tran_end_time			datetime			-- Transaction end time of entry
	--	@tran_id				varbinary(10)		-- Transaction  XDES ID
	--   @tran_begin_lsn			binary(10)		---- begin lsn of the associated transaction
	-- Returns:		
	-- '
	set @stmt = @stmt + N'
	' + @action + N' procedure [cdc].[sp_ins_lsn_time_mapping]  				
	(														
		@start_lsn				binary(10),
		@tran_begin_time		datetime,
		@tran_end_time			datetime,
		@tran_id				varbinary(10),
		@tran_begin_lsn				binary(10)
	)														
	as
	begin
		set nocount on		

		insert into cdc.lsn_time_mapping
		values
		(
			@start_lsn
			,@tran_begin_time
			,@tran_end_time
			,@tran_id
			,@tran_begin_lsn
		)
	end												'
	
	exec (@stmt)	
	exec sp_MS_marksystemobject 'cdc.sp_ins_lsn_time_mapping'
	
	-- Create/Upgrade stored procedure to batch populate cdc.lsn_time_mapping table
	declare @ministmt nvarchar(1000)
	declare @maxrowcount int
	set @maxrowcount = (2100 - 1)/5; --419
       declare @currow int
	
	set @stmt =
	N'
	
	-- Name: [cdc].[sp_batchinsert_lsn_time_mapping]
	
	-- Description:
	--	Stored procedure used internally to batch populate cdc.lsn_time_mapping table
	
	-- Parameters:
	--   @rowcount                     int -- the number of rows to be inserted in the batch, >= 1,and  <= 419
	--	@start_lsn_1                   binary(10)			-- Commit lsn associated with change table entry
	--	@tran_begin_time_1		datetime			-- Transaction begin time of entry
	--	@tran_end_time_1		datetime			-- Transaction end time of entry
	--	@tran_id_1			varbinary(10)		-- Transaction XDES ID
	--   @tran_begin_lsn_1			binary(10)		---- begin lsn of the associated transaction
	--    ...
	--	@start_lsn_419                   binary(10)			-- Commit lsn associated with change table entry
	--	@tran_begin_time_419 	    datetime			-- Transaction begin time of entry
	--	@tran_end_time_419	    datetime			-- Transaction end time of entry
	--	@tran_id_419			    varbinary(10)		-- Transaction XDES ID
	--   @tran_begin_lsn_419			binary(10)		---- begin lsn of the associated transaction
	-- Returns: nothing
	-- '
	set @stmt = @stmt +
	N'
	' + @action + N' procedure [cdc].[sp_batchinsert_lsn_time_mapping]  				
	(
	  @rowcount int,'							

	set @ministmt =
	N'
	  @start_lsn_XXX binary(10), @tran_begin_time_XXX datetime, @tran_end_time_XXX datetime, @tran_id_XXX varbinary(10), @tran_begin_lsn_XXX binary(10)'

	set @currow = 1
	while @currow < @maxrowcount
	begin
		set @stmt = @stmt + REPLACE(@ministmt, N'XXX', convert(nvarchar(3), @currow)) + N','
		set @currow = @currow + 1
	end
	set @stmt = @stmt + REPLACE(@ministmt, N'XXX', convert(nvarchar(3), @currow)) +
	N'
	)														
	as
	begin
	  set nocount on	
	  insert into [cdc].lsn_time_mapping
	  select  top(@rowcount) start_lsn, tran_begin_time, tran_end_time, tran_id, tran_begin_lsn
	  from (
	            select 1 rownum, @start_lsn_1 start_lsn, @tran_begin_time_1 tran_begin_time, @tran_end_time_1 tran_end_time, @tran_id_1 tran_id, @tran_begin_lsn_1 tran_begin_lsn'

	set @ministmt =
	N'
	            union all
	            select XXX, @start_lsn_XXX, @tran_begin_time_XXX, @tran_end_time_XXX,  @tran_id_XXX, @tran_begin_lsn_XXX'

       set @currow = 2
       while @currow <= @maxrowcount
	begin
		set @stmt = @stmt + REPLACE(@ministmt, N'XXX', convert(nvarchar(3), @currow))
		set @currow = @currow + 1
	end

	set @stmt = @stmt +
	N'
	          ) rowcollection
	   where rownum <= @rowcount
	end'

	exec (@stmt)	
	exec dbo.sp_MS_marksystemobject 'cdc.sp_batchinsert_lsn_time_mapping'

	-- Create/Upgrade stored procedure of inserting a dummy entry into cdc.lsn_time_mapping table
	set @stmt =
	N'
	
	-- Name: [cdc].[sp_ins_dummy_lsn_time_mapping]
	
	-- Description: append a dummy entry. A dummy entry has 0x0 for the column tran_id
	
	-- Parameters:
	--	@lastflushed_lsn		binary(10)			
	
	-- Returns:	0	success
	--			1   failure
	-- '
	if object_id(N'[cdc].[sp_ins_dummy_lsn_time_mapping]', N'P') is NULL and @action = N'alter'
		set @action = N'create'
		
	set @stmt = @stmt + N'
	' + @action + N' procedure [cdc].[sp_ins_dummy_lsn_time_mapping ]
	(
		@lastflushed_lsn binary(10) = 0x0
	)
	as
	begin
		set nocount on
		declare 	@cur_time datetime = GETDATE(),
				@dummy_entry_interval int = 300 --default, in seconds

		if @lastflushed_lsn = 0x0
			return (0)

		--avoid inserting duplicate entries
		if exists(select * from [cdc].[lsn_time_mapping] where start_lsn = @lastflushed_lsn)
			return (0)

		--if the last entry was inserted within the interval, skip this dummy entry
		if exists(select * from [cdc].[lsn_time_mapping] where start_lsn = (select max(start_lsn) from [cdc].[lsn_time_mapping])
													and DATEDIFF(second, tran_end_time,  @cur_time) <= @dummy_entry_interval)
		begin
			return (0)
		end

		insert [cdc].[lsn_time_mapping] values(@lastflushed_lsn, @cur_time, @cur_time, 0x0, 0x0)

		if @@error != 0
			return (1)
		else
			return (0)
	end'
	
	exec (@stmt)
	exec dbo.sp_MS_marksystemobject 'cdc.sp_ins_dummy_lsn_time_mapping'

	-- Create/Upgrade stored procedure of inserting a capture instance enabling entry into cdc.lsn_time_mapping table
	set @stmt =
	N'
	
	-- Name: [cdc].[sp_ins_instance_enabling_lsn_time_mapping]
	
	-- Description:query change_tables for the specified capture instance and insert its start_lsn and create_date
	--          into lsn_time_mapping
	
	-- Parameters:
	--	@changetable_objid		int			
	
	-- Returns:	0	success
	--			1   failure
	-- '
	if object_id(N'[cdc].[sp_ins_instance_enabling_lsn_time_mapping]', N'P') is NULL and @action = N'alter'
		set @action = N'create'
		
	set @stmt = @stmt + N'
	' + @action + N' procedure [cdc].[sp_ins_instance_enabling_lsn_time_mapping ]
	(
		@changetable_objid int
	)
	as
	begin
		set nocount on

		insert [cdc].[lsn_time_mapping]
			select start_lsn, create_date, create_date, 0x0, 0x0
			from [cdc].[change_tables]
			where object_id = @changetable_objid

		if @@error != 0
			return (1)
		else
			return (0)
	end'
	
	exec (@stmt)
	exec dbo.sp_MS_marksystemobject 'cdc.sp_ins_instance_enabling_lsn_time_mapping'


end

 
Last revision 2008RTM
See also

  sp_cdc_create_functions (Procedure)
sp_cdc_vupgrade (Procedure)
sp_MSdrop_cdc (Procedure)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash