-- 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