Share via

How to: Validate Data at the Subscriber (Replication Transact-SQL Programming)

  • 發行項

Replication enables you to programmatically validate that data at the Subscriber matches data at the Publisher using replication stored procedures, where the procedures used depend on the type of replication topology.

To validate data for all articles in a transactional publication

  1. At the Publisher on the publication database, execute sp_publication_validation (Transact-SQL). Specify @publication and one of the following values for @rowcount_only:

    • 1 - rowcount check only (the default)
    • 2 - rowcount and binary checksum.
    ms147366.note(zh-tw,SQL.90).gif附註：
    When you execute sp_publication_validation (Transact-SQL), sp_article_validation (Transact-SQL) is executed for each article in the publication. To successfully execute sp_publication_validation (Transact-SQL), you must have SELECT permissions on all columns in the published base tables.

  2. (Optional) Start the Distribution Agent for each subscription if it is not already running. For more information, see 如何：同步處理提取訂閱 (複寫程式設計) and 如何：同步處理發送訂閱 (複寫程式設計).

  3. Check the agent output for the result of the validation. For more information, see 驗證複寫的資料.

To validate data for a single article in a transactional publication

  1. At the Publisher on the publication database, execute sp_article_validation (Transact-SQL). Specify @publication, the name of the article for @article, and one of the following values for @rowcount_only:

    • 1 - Rowcount check only (the default)
    • 2 - Rowcount and binary checksum.
    ms147366.note(zh-tw,SQL.90).gif附註：
    To successfully execute sp_article_validation (Transact-SQL), you must have SELECT permissions on all columns in the published base table.

  2. (Optional) Start the Distribution Agent for each subscription if it is not already running. For more information, see 如何：同步處理提取訂閱 (複寫程式設計) and 如何：同步處理發送訂閱 (複寫程式設計).

  3. Check the agent output for the result of the validation. For more information, see 驗證複寫的資料.

To validate data for a single subscriber to a transactional publication

  1. At the Publisher on the publication database, open an explicit transaction using BEGIN TRANSACTION (Transact-SQL).

  2. At the Publisher on the publication database, execute sp_marksubscriptionvalidation (Transact-SQL). Specify the publication for @publication, the name of the Subscriber for @subscriber, and the name of the subscription database for @destination_db.

  3. (Optional) Repeat step 2 for each subscription being validated.

  4. At the Publisher on the publication database, execute sp_article_validation (Transact-SQL). Specify @publication, the name of the article for @article, and one of the following values for @rowcount_only:

    • 1 - Rowcount check only (the default)
    • 2 - Rowcount and binary checksum.
    ms147366.note(zh-tw,SQL.90).gif附註：
    To successfully execute sp_article_validation (Transact-SQL), you must have SELECT permissions on all columns in the published base table.

  5. At the Publisher on the publication database, commit the transaction using COMMIT TRANSACTION (Transact-SQL).

  6. (Optional) Repeat steps 1 through 5 for each article being validated.

  7. (Optional) Start the Distribution Agent if it is not already running. For more information, see 如何：同步處理提取訂閱 (複寫程式設計) and 如何：同步處理發送訂閱 (複寫程式設計).

  8. Check the agent output for the result of the validation. For more information, see 如何：驗證訂閱者端的資料 (SQL Server Management Studio).

To validate data in all subscriptions to a merge publication

  1. At the Publisher on the publication database, execute sp_validatemergepublication (Transact-SQL). Specify @publication and one of the following values for @level:

    • 1 - Rowcount-only validation.
    • 3 - Rowcount binary checksum validation.

    This marks all subscriptions for validation.

  2. Start the merge agent for each subscription. For more information, see 如何：同步處理提取訂閱 (複寫程式設計) and 如何：同步處理發送訂閱 (複寫程式設計).

  3. Check the agent output for the result of the validation. For more information, see 如何：驗證訂閱者端的資料 (SQL Server Management Studio).

To validate data in selected subscriptions to a merge publication

  1. At the Publisher on the publication database, execute sp_validatemergesubscription (Transact-SQL). Specify @publication, the name of the Subscriber for @subscriber, the name of the subscription database for @subscriber_db, and one of the following values for @level:

    • 1 - Rowcount-only validation.
    • 3 - Rowcount binary checksum validation.

    This marks the selected subscription for validation.

  2. Start the merge agent for each subscription. For more information, see 如何：同步處理提取訂閱 (複寫程式設計) and 如何：同步處理發送訂閱 (複寫程式設計).

  3. Check the agent output for the result of the validation.

  4. Repeat steps 1 through 3 for each subscription being validated.

ms147366.note(zh-tw,SQL.90).gif附註：
A subscription to a merge publication can also be validated at the end of a synchronization by specifying the -Validate parameter when running the Replication Merge Agent.

To validate data in a subscription using Merge Agent parameters

  1. Start the Merge Agent at the Subscriber (pull subscription) or at the Distributor (push subscription) from the command prompt in one of the following ways.

    • Specifying a value of 1 (rowcount) or 3 (rowcount and binary checksum) for the -Validate parameter.
    • Specifying rowcount validation or rowcount and checksum validation for the -ProfileName parameter.

    For more information, see 如何：同步處理提取訂閱 (複寫程式設計) or 如何：同步處理發送訂閱 (複寫程式設計).

請參閱

其他資源

複寫代理程式設定檔
驗證複寫的資料

說明及資訊

取得 SQL Server 2005 協助