This topic has not yet been rated - Rate this topic

sp_publication_validation (Transact-SQL)

SQL Server 2012

Initiates an article validation request for each article in the specified publication. This stored procedure is executed at the Publisher on the publication database.

Topic link icon Transact-SQL Syntax Conventions

Syntax


          

sp_publication_validation [ @publication = ] 'publication'
    [ , [ @rowcount_only = ] type_of_check_requested ]
    [ , [ @full_or_fast = ] full_or_fast ]
    [ , [ @shutdown_agent = ] shutdown_agent ]
    [ , [ @publisher = ] 'publisher' ]

Arguments

[@publication=] 'publication'

Is the name of the publication. publication is sysname, with no default.

[@rowcount_only=] rowcount_only

Is whether to return only the rowcount for the table. rowcount_only is smallint and can be one of the following values.

Value

Description

Perform a SQL Server 7.0 compatible checksum.

Note
When an article is horizontally filtered, a rowcount operation is performed instead of a checksum operation.

1 (default)

Perform a rowcount check only.

Perform a rowcount and binary checksum.

Note
For SQL Server version 7.0 Subscribers, only a rowcount validation is performed.

[@full_or_fast=] full_or_fast

Is the method used to calculate the rowcount. full_or_fast is tinyint and can be one of the following values.

Value	Description
0	Does full count using COUNT(*).
1	Does fast count from sysindexes.rows. Counting rows in sys.sysindexes is much faster than counting rows in the actual table. However, because sys.sysindexes is lazily updated, the rowcount may not be accurate.
2 (default)	Does conditional fast counting by first trying the fast method. If fast method shows differences, reverts to full method. If expected_rowcount is NULL and the stored procedure is being used to get the value, a full COUNT(*) is always used.

[ @shutdown_agent=] shutdown_agent

Is whether the Distribution Agent should shut down immediately upon completion of the validation. shutdown_agent is bit, with a default of 0. If 0, the replication agent does not shut down. If 1, the replication agent shuts down after the last article is validated.

[ @publisher = ] 'publisher'

Specifies a non-SQL Server Publisher. publisher is sysname, with a default of NULL.

Note
publisher should not be used when requesting validation on a SQL Server Publisher.

Return Code Values

0 (success) or 1 (failure)

Remarks

sp_publication_validation is used in transactional replication.

sp_publication_validation can be called at any time after the articles associated with the publication have been activated. The procedure can be run manually (one time) or as part of a regularly scheduled job that validates the data.

If your application has immediate-updating Subscribers, sp_publication_validation may detect spurious errors. sp_publication_validation first calculates the rowcount or checksum at the Publisher and then at the Subscriber. Because the immediate-updating trigger could propagate an update from the Subscriber to the Publisher after the rowcount or checksum is completed at the Publisher, but before the rowcount or checksum is completed at the Subscriber, the values could change. To ensure that the values at the Subscriber and Publisher do not change while validating a publication, stop the Microsoft Distributed Transaction Coordinator (MS DTC) service at the Publisher during validation.

Permissions

Only members of the sysadmin fixed server role or the db_owner fixed database role can execute sp_publication_validation.

Reference

sp_article_validation (Transact-SQL)

sp_table_validation (Transact-SQL)

System Stored Procedures (Transact-SQL)

Concepts

Validate Data at the Subscriber

Did you find this helpful? Yes No

Not accurate

Not enough depth

Need more code examples

(1500 characters remaining)

Community Additions

ADD