Export (0) Print
Expand All
Expand Minimize

sp_marksubscriptionvalidation

SQL Server 2000

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

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft