Replication System Stored Procedures (Transact-SQL)

In SQL Server 2005, many of the existing public replication system stored procedures have been updated to support new replication functionalities. For more information, see Replication Enhancements. The following table lists the new replication stored procedures.

New Replication Stored Procedures

Stored procedure	Description
sp_adddynamicsnapshot_job	Creates an agent job that generates a snapshot for a subscription definition that is based on a parameterized row filter.
sp_addlogreader_agent	Creates a Log Reader agent job for a publication database.
sp_addmergepartition	Creates a partition for a subscription definition that is based on a parameterized row filter using either HOST_NAME or SUSER_SNAME at the Subscriber.
sp_addmergepushsubscription_agent	Creates a new scheduled agent job to synchronize a push subscription to a merge publication.
sp_addpushsubscription_agent	Creates a new scheduled agent job to synchronize a push subscription to a transactional publication.
sp_addqreader_agent	Creates a Queue Reader Agent job for a given Distributor.
sp_changearticlecolumndatatype	Used with Oracle publications. Changes the article column data type mappings between an Oracle Publisher and SQL Server Distributor.
sp_changedynamicsnapshot_job	Modifies the agent job that generates the snapshot for a subscription to a publication with a parameterized row filter.
sp_changelogreader_agent	Changes security properties of a Log Reader Agent job.
sp_changereplicationserverpasswords	Changes stored passwords for the Windows account or SQL Server login used by replication agents when connecting to servers in a replication topology. This stored procedure enables you to change the password for all instances of a given SQL Server login or Windows account used by all replication agents that run at a server.
sp_changesubscription (newly documented)	Changes the properties of a snapshot or transactional push subscription or a pull subscription involved in queued updating transactional replication. Although sp_changesubscription is not a new procedure, it is not documented in earlier releases.
sp_check_dynamic_filters	Displays information on parameterized row filter properties for a publication. In particular, the functions used to generate a filtered data partition for a publication, and whether the publication qualifies for using precomputed partitions.
sp_check_join_filter	Used to verify a join filter between two tables to determine if the join filter clause is valid. This stored procedure also returns information about the supplied join filter, including whether or not it can be used with precomputed partitions for the given table.
sp_check_subset_filter	Used to check a filter clause against any table to determine if the filter clause is valid for the table. This stored procedure returns information about the supplied filter, including whether or not the filter qualifies for use with precomputed partitions.
sp_deletepeerrequesthistory	Deletes history related to a peer-to-peer transactional publication status request.
sp_deletetracertokenhistory	Deletes tracer token history information.
sp_dropdynamicsnapshot_job	Removes a snapshot job for a subscription definition that is based on a parameterized row filter.
sp_dropmergepartition	Removes an existing partition for a subscription definition that is based on a parameterized row filter.
sp_enumeratependingschemachanges	Returns a list of all pending schema changes when replicating schema changes is enabled.
sp_getagentparameterlist	Returns a list of all replication agent parameters that can be set in an agent profile for the specified agent type.
sp_getdefaultdatatypemapping	Returns the default data type mappings between an Oracle Publisher and SQL Server Distributor.
sp_helpdatatypemap	Returns information about publication-specific data type mappings between an Oracle Publisher and SQL Server Distributor.
sp_helpdynamicsnapshot_job	Returns information on agent jobs that generate filtered data snapshots.
sp_helplogreader_agent	Returns properties of the Log Reader Agent job for a publication database.
sp_helpmergepartition	Returns partition information for a specified merge publication.
sp_helppeerrequests	Returns information on all status requests received by participants in a peer-to-peer replication topology.
sp_helppeerresponses	Returns all responses to a specific status request received from a participant in a peer-to-peer replication topology.
sp_helpqreader_agent	Returns properties of the Queue Reader Agent job.
sp_helpsubscriptionerrors	Returns all transactional replication errors for a given subscription.
sp_helptracertokens	Returns one row for each tracer token that has been inserted into a publication to determine latency.
sp_helptracertokenhistory	Returns detailed latency information for specified tracer tokens, with one row being returned for each Subscriber.
sp_helpxactsetjob	Displays information on the Xactset job for an Oracle Publisher.
sp_lookupcustomresolver	Returns the CLSID value of a COM component that is registered as a business logic module used to resolve conflicts.
sp_posttracertoken	Posts a tracer token into the transaction log at the Publisher and begins the process of tracking latency statistics.
sp_register_custom_scripting	Defines customized code that compensates for data definition language (DDL) changes to transactional publications that contain custom user-defined stored procedures.
sp_registercustomresolver	Registers a business logic handler that can be invoked during the merge replication synchronization process.
sp_replmonitorchangepublicationthreshold	Changes the monitoring threshold metric for a publication.
sp_replmonitorhelpmergesession	Returns information on past sessions for a given replication Merge Agent, with one row returned for each session that matches the filtering criterion.
sp_replmonitorhelpmergesessiondetail	Returns detailed, article-level information about a specific replication Merge Agent session, which is used to monitor merge replication.
sp_replmonitorhelppublication	Returns current status information for one or more publications at a Publisher.
sp_replmonitorhelppublicationthresholds	Returns the threshold metrics set for a monitored publication.
sp_replmonitorhelppublisher	Returns current status information for one or more Publishers associated with a Distributor.
sp_replmonitorhelpsubscription	Returns current status information for subscriptions belonging to one or more publications at the Publisher, and returns one row for each returned subscription.
sp_replmonitorsubscriptionpendingcmds	Returns information on the number of pending commands for a subscription to a transactional publication, and a rough estimate of how much time it takes to process them.
sp_requestpeerresponse	When executed from a node in a peer-to-peer topology, this procedure requests a response from every other node in the topology. By executing this procedure and reviewing the corresponding responses, you can guarantee that all previous commands have been delivered to the responding nodes.
sp_resetsnapshotdeliveryprogress	Resets the snapshot delivery process for a pull subscription so that snapshot delivery can be restarted.
sp_schemafilter	Modifies and displays information on the schema that is excluded when listing Oracle tables eligible for publishing.
sp_setdefaultdatatypemapping	Marks an existing data type mapping between Microsoft SQL Server and a non-SQL Server database management system (DBMS) as the default.
sp_showpendingchanges	Returns a result set showing an approximate number of changes that are waiting to be replicated.
sp_startpublication_snapshot	Starts the Snapshot Agent job that generates the initial snapshot for a publication.
sp_unregister_custom_scripting	Removes custom code that was registered by executing sp_register_custom_scripting.
sp_unregistercustomresolver	Removes a previously registered business logic handler.

See Also

Other Resources

What's New and Enhanced in Transact-SQL (Transact-SQL)
How to: Upgrade Replication Scripts (Replication Transact-SQL Programming)

Help and Information

Getting SQL Server 2005 Assistance