sp_marksubscriptionvalidation

Transact-SQL Reference

Transact-SQL Reference

sp_marksubscriptionvalidation

Marks the current open transaction to be a subscription level validation transaction for the specified subscriber. It must be used together with sp_article_validation having @subscription_level equal to 1. It can be used with other calls to sp_marksubscriptionvalidation to mark the current open transaction for other subscribers. This stored procedure is executed at the Publisher on the publication database.

Syntax

sp_marksubscriptionvalidation [ @publication = ] 'publication'
    , [ @subscriber = ] 'subscriber'
    , [ @destination_db = ] 'destination_db'

Arguments

[@publication = ] 'publication'

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

[@subscriber = ] 'subscriber'

Is the name of the Subscriber. subscriber is sysname, with no default.

[@destination_db =] 'destination_db'

Is the name of the destination database. destination_db is sysname, with no default.

Return Code Values

0 (success) or 1 (failure)

Remarks

sp_marksubscriptionvalidation

Is used in all types of replication. This stored procedure does not support heterogeneous Subscribers.

Permissions

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

Examples

The following query can be applied to the publishing database to post subscription-level validation commands. These commands are picked up by the Distribution Agents of specified Subscribers.

begin tran

exec sp_marksubscriptionvalidation @publication = 'pub1',
 @subscriber = 'Sub', @destination_db = 'SubDB'

exec sp_marksubscriptionvalidation @publication = 'pub1',
 @subscriber = 'Sub2', @destination_db = 'SubDB'

exec sp_article_validation @publication = 'pub1', @article = 'art1',
 @rowcount_only = 0, @full_or_fast = 0, @shutdown_agent = 0,
 @subscription_level = 1

commit tran

begin tran

exec sp_marksubscriptionvalidation @publication = 'pub1',
 @subscriber = 'Sub', @destination_db = 'SubDB'

exec sp_marksubscriptionvalidation @publication = 'pub1',
 @subscriber = 'Sub2', @destination_db = 'SubDB'

exec sp_article_validation @publication = 'pub1', @article = 'art2',
 @rowcount_only = 0, @full_or_fast = 0, @shutdown_agent = 0,
 @subscription_level = 1

commit tran

Note that the first transaction validates article 'art1', while the second transaction validates 'art2'. Also note that the calls to sp_marksubscriptionvalidation and sp_articlevalidation have been encapsulated in a transaction. It is advised that there is only one call to sp_articlevalidation per transaction. This is because sp_article_validation holds a shared table lock on the source table for the duration of the transaction. You should keep the transaction short to maximize concurrency.

See Also

System Stored Procedures

Validating Replicated Data