Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSrepl_posttracertoken

  No additional text.


Syntax
create procedure sys.sp_MSrepl_posttracertoken
(
	@publication 		sysname,
	@tracer_token_id 	int output,
	@publisher 			sysname,
	@publisher_type 	sysname
)
as
begin
	set nocount on

	declare @retcode 				int,
			@newid					uniqueidentifier,
			@rpc_cmd				nvarchar(4000),
			@distributor 			sysname,
			@distribution_db		sysname,
			@publisher_db			sysname,
			@publication_id			int,
			@publication_type		int,
			@article_id				int,
			@tracer_str				nvarchar(32),
			@subscribers_found		bit,
			@INACTIVE				int,
			@ACTIVE					int,
			@SNAPSHOT_PUB			int,
			@TRACER_CMD_TYPE		int

	select @retcode 				= 0,
			@rpc_cmd				= null,
			@distributor 			= null,
			@distribution_db		= null,
			@publisher_db			= db_name(),
			@publication_id			= null,
			@publication_type		= @SNAPSHOT_PUB,
			@article_id				= null,
			@tracer_token_id 		= null,
			@subscribers_found		= 0,
			@INACTIVE				= 0,
			@ACTIVE					= 1,
			@SNAPSHOT_PUB			= 1,
			@TRACER_CMD_TYPE		= 47
			
	-- security check
	exec @retcode = sys.sp_MSreplcheck_publish
	if @@error <> 0 or @retcode <> 0
	begin
		return 1
	end	
	
	-- check this db is published
	if sys.fn_MSrepl_istranpublished(db_name(),1) <> 1
	begin
		-- "The database is not published."
		raiserror (18757, 16, -1)
		return 1
	end
	
	-- check publication name
	if @publication is null
    begin
		-- "The parameter @publication cannot be NULL."
        raiserror (14043, 16, -1, @publication, 'sp_MSrepl_posttracertoken')
        return 1
    end
	
	-- validate publication settings
	select @publication_id = pubid,
			@publication_type = repl_freq
		from syspublications
		where name = @publication

	if @publication_id is null
	begin
		-- "The publication '@publication' does not exist."
		raiserror (20026, 16, -1, @publication)
		return 1
	end
	
	if @publication_type = @SNAPSHOT_PUB
	begin	
		-- "Tracer tokens can not be posted for a snapshot publication."
		raiserror(21485, 16, -1)
		return 1
	end
	
	-- check for an article id
	select @article_id	= min(artid) from sysextendedarticlesview where pubid = @publication_id
	if @article_id is null
	begin
		-- "There are no articles for publication '@publication'."
		raiserror (14009, 16, -1, @publication)
		return 1
	end

	-- if SQL Server publisher, set @publisher to publishingservername()
	if @publisher IS NULL
	begin
		SET @publisher = publishingservername()
	end

	-- retrieve this publishers distributor and distribution DB info
    exec @retcode = sys.sp_MSrepl_getdistributorinfo @publisher		= @publisher,
													@rpcsrvname		= @distributor output,
													@distribdb		= @distribution_db output
    if @@error <> 0 or @retcode <> 0 or @distributor is null
	begin
		-- "Could not find the Distributor or the distribution database for the local server. The Distributor may not be installed, or the local server may not be configured as a Publisher at the Distributor."
		raiserror (14071, 16, -1)
		return 1
    end

    begin transaction tr_sp_posttracertoken
	save transaction tr_sp_posttracertoken

	-- log tracer token stat information at the distributor
	select @rpc_cmd = quotename(@distributor)
						+ N'.' + quotename(@distribution_db)
						+ N'.sys.sp_MSadd_tracer_token'
	
	exec @retcode = @rpc_cmd @publisher				= @publisher,
								@publisher_db		= @publisher_db,
								@publication		= @publication,
								@tracer_id 			= @tracer_token_id output,
								@subscribers_found	= @subscribers_found output
	if @@error <> 0 or @retcode <> 0
	begin
		-- "An error occured while logging the tracer token history information. The tracer token could not be posted."
		raiserror (21486, 16, -1)
		goto FAILURE
	end

	-- if no active subscribers found then report the error and exit
	if @subscribers_found = 0
	begin
		-- "No active subscriptions found! The publication must have active subscriptions in order to post a tracer token."
		raiserror (21488, 16, -1)
		goto FAILURE
	end
	
	-- For a heterogeneous publisher, bypass the post here.  The publisher will be
	-- contacted outside of the transaction to setup the appropriate event.
	if @publisher_type = N'MSSQLSERVER'
	begin
		-- Post the tracer token in the log prior to posting history for the subscribers
		-- this ensures that the token makes it into the log before we setup the list of
		-- subscribers that should recieve the token. if list creation fails we can rollback
		select @tracer_str = cast(@tracer_token_id as nvarchar)
	
		exec @retcode = sys.sp_replpostcmd 0, @publication_id, @article_id, @TRACER_CMD_TYPE, @tracer_str
		if @@error <> 0 or @retcode <> 0
		begin
			-- "An error occured while inserting the tracer token to the log. The tracer token could not be posted."
    		raiserror (21487, 16, -1, @publication)
			goto FAILURE
		end
	end

	-- create the list of subscribers that are expected to recieve the tracer token
	select @rpc_cmd = quotename(@distributor)
						+ N'.' + quotename(@distribution_db)
						+ N'.sys.sp_MSadd_tracer_history'
	
	exec @retcode = @rpc_cmd @tracer_id = @tracer_token_id
	if @@error <> 0 or @retcode <> 0
	begin
		-- "An error occured while logging the tracer token history information. The tracer token could not be posted."
		raiserror (21486, 16, -1)
		goto FAILURE
	end

	commit transaction tr_sp_posttracertoken

	
	-- NOTE :  DO NOT ALLOW THIS CALL IN A TRANSACTION!!!!
	
	-- For a heterogeneous publisher, we insert a tracer event into the publisher's event table.
	-- The log reader will process the event when the xactset that the event is associated with
	-- is processed.
	if @publisher_type LIKE 'ORACLE%'
	begin
		select @tracer_str = cast(@tracer_token_id as nvarchar)
	
		exec @retcode = dbo.sp_ORAposttracertoken @publication_id, @article_id, @TRACER_CMD_TYPE, @tracer_str, @publisher
		if @@error <> 0 or @retcode <> 0
		begin
			-- "An error occured while inserting the tracer token into the log. The tracer token could not be posted."
    		raiserror (21487, 16, -1, @publication)
			return 1
		end
	end

SUCCESS:
	return 0
	
FAILURE:
	rollback transaction tr_sp_posttracertoken
	commit transaction tr_sp_posttracertoken

	return 1
end

 
Last revision 2008RTM
See also

  sp_MSdrop_repltran (Procedure)
sp_posttracertoken (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