Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_IHarticle_validation

  No additional text.


Syntax


-- Name:
--          sp_IHarticle_validation

-- Description:
--          Performs HREPL article validation steps

-- Security:
--          Internal
-- Requires Certificate signature for catalog access

-- Returns:
--          Success/failure

-- Owner:
--          

CREATE PROCEDURE sys.sp_IHarticle_validation
(
	@publication		sysname,
	@article			sysname,
	@rowcount_only		smallint = 1,	-- type of check requested
	@full_or_fast		tinyint = 2,	-- full (0) fast (1), or conditional fast (2)  method to calculate rowcount
	@shutdown_agent		bit = 0,		-- shut down agent after validation if 1
	@subscription_level	bit = 0,		-- Whether or not the validation is only picked up by a set of subscribers
	@reserved			int = NULL,		-- If not null, the sp is called from sp_publication_validation
	@publisher			sysname,
	@publisher_type		sysname
)
AS
BEGIN
	set nocount on

	declare @publication_id int
	declare @article_id int
	declare @source_name sysname
	declare @source_owner sysname
	declare @destination_table sysname
	declare @destination_owner sysname
	declare @publisher_db sysname
	declare @command nvarchar(4000)
	declare @markercommand varchar(1024)
	declare @retcode int
	declare @status int
	declare @active int
	declare @repl_freq int
	declare @actual_rowcount int
	declare @publisher_id int
	declare @xact_seqno varbinary(16)
	declare @xact_id varbinary(16)
	declare @binarycommand varbinary(1024)

	set @active = 1

	-- Check if the database is published for transactional
	IF sys.fn_MSrepl_istranpublished(DB_NAME(),1) <> 1
	begin
		raiserror(20026, 16, -1, @publication)
		return 1
	end

	-- Get Publication Information
	select	@publication_id = pubid,
			@repl_freq = repl_freq
	from	syspublications p,
			master.dbo.sysservers s,
			MSpublications m
	where	QUOTENAME(UPPER(@publisher)) = QUOTENAME(UPPER(s.srvname collate database_default))
	  and	m.publisher_id = s.srvid
	  and	m.publication_id = p.pubid
	  and	m.publication = p.name
	  and	m.publication = @publication
	
	if @publication_id is null
	begin
		raiserror(20026, 16, -1, @publication)
    	return 1
	end
	
	-- Article validation is not valid for snapshot publications
	if @repl_freq <> 0
	begin
		raiserror(21484, 16, -1, @publication)
		return 1
	end

	-- Get Article Information
	select	@article_id			= artid,
			@destination_table	= dest_table,
			@destination_owner	= dest_owner,
			@status				= status,
			@source_name		= m.source_object,
			@source_owner		= m.source_owner
	from	sysarticles a,
			master.dbo.sysservers s,
			MSarticles m
	where	a.name  = @article
	  and	a.pubid = @publication_id
	  and	a.name  = m.article
	  and	a.pubid = m.publication_id
	  and	a.artid = m.article_id
	  and	s.srvid = m.publisher_id
	  and	QUOTENAME(UPPER(s.srvname collate database_default)) = QUOTENAME(UPPER(@publisher))
				
	if @article_id is null
	begin
		raiserror(20027, 16, -1, @article)
		return 1
	end

	-- Make sure article status is 'active'
	if (@status & @active) <> @active
	begin
		-- Article is not active
   		raiserror(20523, 16, -1, @article)
		return 1
	end

	-- Make sure row count validation was requested
	if @rowcount_only <> 1
	begin
   		raiserror(21650, 16, -1, @article)
		return 1
	end

	-- Make sure the rowcount method requested is 0, 1, or 2
	if @full_or_fast NOT IN(0, 1, 2)
	begin
   		raiserror(21660, 16, -1, @article)
		return 1
	end
	
	-- Make sure the shutdown agent bit is 0 or 1
	if @shutdown_agent IS NULL
	begin
   		raiserror(21661, 16, -1, @article)
    	return 1
	end

	declare @command_type int
	if @subscription_level = 0
    	select @command_type = 35 -- SQL Server Only command type
	else
    	select @command_type = 69 -- sub validation command
	
	-- Call sp_ORArowcount to generate the request to determine the row count and post event to the log reader.
	-- Generate sp_table_validation command template that will be sent on behalf of the article to subscribers
	select @command = 'exec dbo.sp_table_validation @table = '
						+ QUOTENAME(@destination_table, '''')
						+ N', @expected_rowcount = %d, @rowcount_only = 1, @full_or_fast = '
						+ convert(nvarchar(10), @full_or_fast)
						+ N', @shutdown_agent = ' + convert(nvarchar(10), @shutdown_agent)

	-- Add owner param if destination owner is not NULL
	if (@destination_owner IS NOT NULL)
	begin
   		select @command = @command
   							+ ', @owner = '
   							+ QUOTENAME(@destination_owner, '''')
	end

	-- Generate the row count request at the publisher
   	exec @retcode = sp_ORArowcount	@publisher,
   									@source_owner,
   									@source_name,
   									@publication_id,
   									@article_id,
   									@command_type,
   									@command,
   									@subscription_level
        	
   	if @retcode <> 0 or @@error <> 0
    	return 1

	-- Return message indicating row count request has been submitted to publisher.
   	raiserror (20514, 10, -1, @publisher, @article, @publication)

	return 0
end

 
Last revision 2008RTM
See also

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