Home Microsoft SQL Server DigiMailing Contact
    Keyword



sys.sp_MSarticle_validation

  No additional text.


Syntax
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


 
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