sp_showpendingchanges (Transact-SQL)

SQL Server 2005 Books Online (November 2008)

Updated: 17 November 2008

Returns a result set showing an approximate number of changes that are waiting to be replicated. This stored procedure is executed at the Publisher on the publication database and at the Subscriber on the subscription database.

Topic link icon Transact-SQL Syntax Conventions

Syntax

sp_showpendingchanges [ [ @destination_server = ] 'destination_server' ]
    [ , [ @publication = ] 'publication' ]
    [ , [ @article = ] 'article']
    [ , [ @show_rows = ] show_rows ]

Arguments

[ @destination_server = ] 'destination_server': Is the name of the server where the replicated changes are applied. destination_server is sysname, with default value of NULL.

[ @publication = ] 'publication': Is the name of the publication. publication is sysname, with a default value of NULL. When publication is specified, results are limited only to the specified publication.

[ @article = ] 'article': Is the name of the article. article is sysname, with a default value of NULL. When article is specified, results are limited only to the specified article.

[ @show_rows = ] show_rows: Specifies whether the result set contains more specific information about pending changes, with a default value of 0. If a value of 1 is specified, the result set contains the columns is_delete and rowguid.

Return Code Values

0 (success) or 1 (failure)

Remarks

sp_showpendingchanges is used in merge replication.

sp_showpendingchanges is used when troubleshooting merge replication.

The result of sp_showpendingchanges does not include rows in generation 0.

When an article specified for article does not belong to the publication specified for publication, a count of 0 is returned for pending_deletes and pending_ins_and_upd.

Permissions

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

Result Set

Column name	Data type	Description
destination_server	sysname	The name of the server to which the changes are being replicated.
pub_name	sysname	The name of the publication.
destination_db_name	sysname	The name of the database to which the changes are being replicated.
is_dest_subscriber	bit	Indicates of the changes are being replicated to a Subscriber. A value of 1 indicates that the changes are being replicated to a Subscriber. 0 means that changes are being replicated to a Publisher.
article_name	sysname	The name of the article for the table where changes originated.
pending_deletes	int	The number of deletes waiting to be replicated.
pending_ins_and_upd	int	The number of inserts and updates waiting to be replicated.
is_delete	bit	Indicates whether the pending change is a delete. A value of 1 indicates that the change is a delete. Requires a value of 1 for @show_rows.
rowguid	uniqueidentifier	The GUID that identifies the row that changed. Requires a value of 1 for @show_rows.

Reference

Replication Stored Procedures (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release	History
17 November 2008	New content: Added the @show_rows parameter to the Syntax and Arguments sections, and the is_delete and rowguid columns to the Result Set section.