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.