create procedure sys.sp_MSarticle_validation
@publication sysname,
@article sysname,
-- The following are values passed to the sp_table_validation call at the subscriber.
@rowcount_only smallint = 1,
/*
The @rowcount_only param is overloaded for shiloh release due to backward compatibility concerns.
In shiloh, the checksum functionality has changed. So 7.0 subscribers will have the old checksum
routines, which generate different CRC values, and do not have functionality for vertical partitions,
or logical table structures where column offsets differ (due to ALTER TABLEs that DROP and ADD columns).
In 7.0, this was a bit column. 0 meant do not do just a rowcount - do a checksum. 1 meant just do a
rowcount.
For Shiloh, this parameter is changed to a smallint with these options:
0 - Do a 7.0 compatible checksum
1 - Do a rowcount check only
2 - Use new Shiloh checksum functionality. Note that because 7.0 subscribers will
take this parameter as a bit type, not a smallint, it will be interpreted as simply
ON. That means that passing a 2, and having a 7.0 subscriber, will result in the 7.0
subscriber doing only rowcount validation. The Shiloh subscribers will do both
rowcount and checksum. If you want 7.0 subscribers to do checksum validation, use
the value of 0 for this parameter. Shiloh subscribers can do the 7.0 compatible
checksum, but that checksum has the same 7.0 limitations for vertical partitions
and differences in physical table structure.)
*/
@full_or_fast tinyint = 2, -- full (value 0) does COUNT(*)
-- fast (value 1) uses sysindexes.rows if table (not view);
-- conditional fast (VALUE 2) , first tries fast method, but
-- reverts to full if fast method shows differences.
@shutdown_agent bit = 0, -- If 1 will raise error 20578, which will signal subscriber synchronization agent to shutdown
-- immediately after successful validation
@subscription_level bit = 0 -- Whether or not the validation is only picked up by a set of subscribers
-- that are specified by calls to sp_marksubscriptionvalidation.
, @reserved int = NULL -- If not null, the sp is called from sp_publication_validation.
as
declare @publication_guid uniqueidentifier
declare @publication_id int
declare @article_guid uniqueidentifier
declare @article_id int
declare @source_name sysname
declare @source_owner sysname
declare @partition_view_id int, @table_id int
declare @sync_name sysname
declare @destination_table sysname
declare @destination_owner sysname
declare @command varchar (4096)
declare @retcode int
declare @actual_rowcount bigint
declare @actual_checksum numeric
declare @status int
declare @active int
declare @repl_freq int
declare @publish_bit int
declare @table_name sysname -- base table name var to passed to sp_table_validation
, @allow_dts bit
, @dts_part int
, @horizontally_partitioned bit
, @vertically_partitioned bit
, @filter_clause nvarchar(max)
, @column_list nvarchar(max)
, @include_timestamp bit
, @manual_view bit
set nocount on
set @active = 1
set @publish_bit = 1
set @dts_part = 64
-- Check if the database is published for transactional
if not exists (select * from master.dbo.sysdatabases where name = db_name() collate database_default and (category & @publish_bit) = @publish_bit)
begin
raiserror(20026, 16, -1, @publication)
return 1
end
-- Get Publication Information
select @publication_id = pubid, @allow_dts = allow_dts, @repl_freq = repl_freq from syspublications where name = @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, @sync_name = OBJECT_NAME(sync_objid), @partition_view_id = sync_objid,
@destination_table = dest_table, @destination_owner = dest_owner,
@status = status, @table_name = OBJECT_NAME(objid),@table_id=objid,
@filter_clause = case when filter <> 0 then filter_clause else null end,
@include_timestamp = case when (status & 32 <> 0) then 1 else 0 end,
@horizontally_partitioned = case when filter <> 0 then 1 else 0 end,
@manual_view = case when (type & 4) = 4 then 1 else 0 end,
@vertically_partitioned = convert(bit, case when (art.type & 1) <> 1 or
not exists (select *
from sys.columns cols
where cols.object_id = art.objid
and not ((art.status & 32)=32 and cols.system_type_id = 189)
and cols.column_id not in (select artcols.colid
from dbo.sysarticlecolumns artcols
where artcols.artid = art.artid)) then 0
else 1 end)
from sysarticles art where name = @article and pubid=@publication_id
if @article_id is null
begin
raiserror(20027, 16, -1, @article)
return 1
end
-- Security check
-- Only people have 'select all' permission on the base table can do validation
if permissions(@table_id) & 1 <> 1
begin
declare @qual_name nvarchar(517)
exec sys.sp_MSget_qualified_name @table_id, @qual_name output
raiserror(20623, 16, -1, @article, @qual_name)
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
if @allow_dts = 1
begin
if @rowcount_only <> 1
begin
raiserror(20612, 16, -1)
return (1)
end
-- For dts horizontal partitioned article, no validation is possible, do nothing or
-- raise error.
if (@status & @dts_part <> 0)
begin
-- sp_article_validation is called directly, raise error and fail
if @reserved is null
begin
raiserror(20613, 16, -1)
return (1)
end
-- sp_article_validation is called by sp_publication_validation,
-- raise warning and contiue.
else
begin
raiserror(20613, 10, -1)
return (0)
end
end
end
if @manual_view = 0
begin
set @source_name = @table_name
select @source_owner= schema_name(schema_id) from sys.objects where object_id=@table_id
end
else
begin
set @source_name = @sync_name
select @source_owner= schema_name(schema_id) from sys.objects where object_id=@partition_view_id
end
-- Check if table has vertical partition OR horizontal partition
if @horizontally_partitioned <> 0 OR @vertically_partitioned <> 0 or @manual_view <> 0
begin
-- Partitions only support new shiloh checksum functionality or row count validation.
-- If 7.0 compatible checksum was asked for (@rowcount_only=0), it will be changed to
-- override specified value, making it truly only check rowcounts. If shiloh checksum
-- was asked for, thats ok (@rowcount_only=2), thats ok and no need to change it.
if (@rowcount_only = 0)
set @rowcount_only = 1
end
-- Get column list if article is vertically partitioned
if @vertically_partitioned <> 0
begin
exec @retcode = sys.sp_MSgettransarticlecolumnlist
@article_id = @article_id,
@include_timestamp = @include_timestamp,
@column_list = @column_list output
end
else
begin
set @column_list = null
end
begin tran -- The table validation and posting to the log MUST happen with a transaction
-- Get publisher's rowcount and/or checksum for the article
if @rowcount_only = 1
begin
exec @retcode = sys.sp_MStable_validation @table = @source_name, @expected_rowcount = @actual_rowcount OUTPUT,
@rowcount_only = 1, @owner=@source_owner, @full_or_fast = 0, @table_name = @table_name, @column_list = @column_list, @filter_clause = @filter_clause -- always do full count at publisher
if @retcode <> 0 or @@error <> 0
begin
commit tran
return 1
end
end
else -- get checksum
begin
exec @retcode = sys.sp_MStable_validation @table = @source_name, @expected_rowcount = @actual_rowcount OUTPUT,
@expected_checksum = @actual_checksum OUTPUT, @rowcount_only = @rowcount_only, @owner=@source_owner,
@full_or_fast = 0, @table_name = @table_name, @column_list = @column_list, @filter_clause = @filter_clause -- always do full count at publisher
if @retcode <> 0 or @@error <> 0
begin
commit tran
return 1
end
end
-- Post sp_table_validation on behalf of the article and send to subscribers
if @rowcount_only = 1
begin
select @command = 'exec dbo.sp_table_validation @table = ''' + replace(@destination_table, '''', '''''') + ''', @expected_rowcount = ' +
convert(varchar(10), @actual_rowcount) + ', @rowcount_only = 1' +
', @full_or_fast = ' + convert(varchar(10), @full_or_fast) +
', @shutdown_agent = ' + convert(varchar(10), @shutdown_agent)
end
else
begin
select @command = 'exec dbo.sp_table_validation @table = ''' + replace(@destination_table, '''', '''''') + ''', @expected_rowcount = ' +
convert(varchar(10), @actual_rowcount) + ', @expected_checksum = ' +
convert(varchar(100), @actual_checksum) + ', @rowcount_only = ' + convert(varchar(5),@rowcount_only) +
', @full_or_fast = ' + convert(varchar(10), @full_or_fast) +
', @shutdown_agent = ' + convert(varchar(10), @shutdown_agent)
end
-- Add owner param if destination owner is not NULL
if (@destination_owner IS NOT NULL)
begin
select @command = @command +
', @owner = ''' + replace(@destination_owner, '''', '''''') + ''''
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
exec @retcode = sys.sp_replpostcmd
0, -- partial flag
@publication_id,
@article_id,
@command_type, -- SQL Server Only command type
@command
if @retcode <> 0 or @@error <> 0
begin
commit tran
return 1
end
commit tran
