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