SQL Server

United States (English) Sign in

Print This Topic Print Multiple Topics

Home 2012 2008 R2 2008 Previous Versions Library Forums

TechNet Library

SQL Server

SQL Server 2012

Product Documentation

Books Online for SQL Server 2012

Replication

Technical Reference

Replication Stored Procedures (Transact-SQL)

sp_add_agent_parameter (Transact-SQL)

sp_add_agent_profile (Transact-SQL)

sp_addarticle (Transact-SQL)

sp_adddistpublisher (Transact-SQL)

sp_adddistributiondb (Transact-SQL)

sp_adddistributor (Transact-SQL)

sp_adddynamicsnapshot_job (Transact-SQL)

sp_addlogreader_agent (Transact-SQL)

sp_addmergealternatepublisher (Transact-SQL)

sp_addmergearticle (Transact-SQL)

sp_addmergefilter (Transact-SQL)

sp_addmergepartition (Transact-SQL)

sp_addmergepublication (Transact-SQL)

sp_addmergepullsubscription (Transact-SQL)

sp_addmergepullsubscription_agent (Transact-SQL)

sp_addmergepushsubscription_agent (Transact-SQL)

sp_addmergesubscription (Transact-SQL)

sp_addpublication (Transact-SQL)

sp_addpublication_snapshot (Transact-SQL)

sp_addpullsubscription (Transact-SQL)

sp_addpullsubscription_agent (Transact-SQL)

sp_addpushsubscription_agent (Transact-SQL)

sp_addqreader_agent (Transact-SQL)

sp_addqueued_artinfo (Transact-SQL)

sp_addscriptexec (Transact-SQL)

sp_addsubscriber (Transact-SQL)

sp_addsubscriber_schedule (Transact-SQL)

sp_addsubscription (Transact-SQL)

sp_addsynctriggers (Transact-SQL)

sp_addtabletocontents (Transact-SQL)

sp_adjustpublisheridentityrange (Transact-SQL)

sp_article_validation (Transact-SQL)

sp_articlecolumn (Transact-SQL)

sp_articlefilter (Transact-SQL)

sp_articleview (Transact-SQL)

sp_attachsubscription (Transact-SQL)

sp_browsesnapshotfolder (Transact-SQL)

sp_browsemergesnapshotfolder (Transact-SQL)

sp_browsereplcmds (Transact-SQL)

sp_change_agent_parameter (Transact-SQL)

sp_change_agent_profile (Transact-SQL)

sp_changearticle (Transact-SQL)

sp_changearticlecolumndatatype (Transact-SQL)

sp_changedistpublisher (Transact-SQL)

sp_changedistributiondb (Transact-SQL)

sp_changedistributor_password (Transact-SQL)

sp_changedistributor_property (Transact-SQL)

sp_changedynamicsnapshot_job (Transact-SQL)

sp_changelogreader_agent (Transact-SQL)

sp_changemergearticle (Transact-SQL)

sp_changemergefilter (Transact-SQL)

sp_changemergepublication (Transact-SQL)

sp_changemergepullsubscription (Transact-SQL)

sp_changemergesubscription (Transact-SQL)

sp_changepublication (Transact-SQL)

sp_changepublication_snapshot (Transact-SQL)

sp_changeqreader_agent (Transact-SQL)

sp_changereplicationserverpasswords (Transact-SQL)

sp_changesubscriber (Transact-SQL)

sp_changesubscriber_schedule (Transact-SQL)

sp_changesubscriptiondtsinfo (Transact-SQL)

sp_changesubscription (Transact-SQL)

sp_changesubstatus (Transact-SQL)

sp_change_subscription_properties (Transact-SQL)

sp_check_dynamic_filters (Transact-SQL)

sp_check_for_sync_trigger (Transact-SQL)

sp_check_join_filter (Transact-SQL)

sp_check_subset_filter (Transact-SQL)

sp_configure_peerconflictdetection (Transact-SQL)

sp_copymergesnapshot (Transact-SQL)

sp_copysnapshot (Transact-SQL)

sp_copysubscription (Transact-SQL)

sp_deletemergeconflictrow (Transact-SQL)

sp_deletepeerrequesthistory (Transact-SQL)

sp_deletetracertokenhistory (Transact-SQL)

sp_drop_agent_parameter (Transact-SQL)

sp_drop_agent_profile (Transact-SQL)

sp_dropanonymousagent (Transact-SQL)

sp_droparticle (Transact-SQL)

sp_dropdistpublisher (Transact-SQL)

sp_dropdistributiondb (Transact-SQL)

sp_dropdistributor (Transact-SQL)

sp_dropdynamicsnapshot_job (Transact-SQL)

sp_dropmergealternatepublisher (Transact-SQL)

sp_dropmergearticle (Transact-SQL)

sp_dropmergefilter (Transact-SQL)

sp_dropmergepartition (Transact-SQL)

sp_dropmergepublication (Transact-SQL)

sp_dropmergepullsubscription (Transact-SQL)

sp_dropmergesubscription (Transact-SQL)

sp_droppublication (Transact-SQL)

sp_droppullsubscription (Transact-SQL)

sp_dropsubscriber (Transact-SQL)

sp_dropsubscription (Transact-SQL)

sp_dsninfo (Transact-SQL)

sp_enumcustomresolvers (Transact-SQL)

sp_enumeratependingschemachanges (Transact-SQL)

sp_enumdsn (Transact-SQL)

sp_expired_subscription_cleanup (Transact-SQL)

sp_generatefilters (Transact-SQL)

sp_get_distributor (Transact-SQL)

sp_get_redirected_publisher (Transact-SQL)

sp_getagentparameterlist (Transact-SQL)

sp_getdefaultdatatypemapping (Transact-SQL)

sp_getmergedeletetype (Transact-SQL)

sp_getqueuedrows (Transact-SQL)

sp_getsubscriptiondtspackagename (Transact-SQL)

sp_gettopologyinfo (Transact-SQL)

sp_grant_publication_access (Transact-SQL)

sp_help_agent_default (Transact-SQL)

sp_help_agent_parameter (Transact-SQL)

sp_help_agent_profile (Transact-SQL)

sp_help_peerconflictdetection (Transact-SQL)

sp_help_publication_access (Transact-SQL)

sp_helparticle (Transact-SQL)

sp_helparticlecolumns (Transact-SQL)

sp_helparticledts (Transact-SQL)

sp_helpdatatypemap (Transact-SQL)

sp_helpdistpublisher (Transact-SQL)

sp_helpdistributiondb (Transact-SQL)

sp_helpdistributor (Transact-SQL)

sp_helpdistributor_properties (Transact-SQL)

sp_helpdynamicsnapshot_job (Transact-SQL)

sp_helplogreader_agent (Transact-SQL)

sp_helpmergealternatepublisher (Transact-SQL)

sp_helpmergearticle (Transact-SQL)

sp_helpmergearticlecolumn (Transact-SQL)

sp_helpmergearticleconflicts (Transact-SQL)

sp_helpmergeconflictrows (Transact-SQL)

sp_helpmergedeleteconflictrows (Transact-SQL)

sp_helpmergefilter (Transact-SQL)

sp_helpmergepartition (Transact-SQL)

sp_helpmergepublication (Transact-SQL)

sp_helpmergepullsubscription (Transact-SQL)

sp_helpmergesubscription (Transact-SQL)

sp_helppeerrequests (Transact-SQL)

sp_helppeerresponses (Transact-SQL)

sp_helppublication (Transact-SQL)

sp_helppublication_snapshot (Transact-SQL)

sp_helppullsubscription (Transact-SQL)

sp_helpqreader_agent (Transact-SQL)

sp_helpreplfailovermode (Transact-SQL)

sp_helpreplicationdboption (Transact-SQL)

sp_helpreplicationoption (Transact-SQL)

sp_helpsubscriberinfo (Transact-SQL)

sp_helpsubscription (Transact-SQL)

sp_helpsubscription_properties (Transact-SQL)

sp_helpsubscriptionerrors (Transact-SQL)

sp_helptracertokens (Transact-SQL)

sp_helptracertokenhistory (Transact-SQL)

sp_helpxactsetjob (Transact-SQL)

sp_ivindexhasnullcols (Transact-SQL)

sp_link_publication (Transact-SQL)

sp_lookupcustomresolver (Transact-SQL)

sp_markpendingschemachange (Transact-SQL)

sp_marksubscriptionvalidation (Transact-SQL)

sp_mergearticlecolumn (Transact-SQL)

sp_mergecleanupmetadata (Transact-SQL)

sp_mergedummyupdate (Transact-SQL)

sp_mergemetadataretentioncleanup (Transact-SQL)

sp_mergesubscription_cleanup (Transact-SQL)

sp_MSchange_distribution_agent_properties (Transact-SQL)

sp_MSchange_merge_agent_properties (Transact-SQL)

sp_MSchange_logreader_agent_properties (Transact-SQL)

sp_MSchange_snapshot_agent_properties (Transact-SQL)

sp_posttracertoken (Transact-SQL)

sp_publication_validation (Transact-SQL)

sp_publisherproperty (Transact-SQL)

sp_redirect_publisher (Transact-SQL)

sp_refreshsubscriptions (Transact-SQL)

sp_register_custom_scripting (Transact-SQL)

sp_registercustomresolver (Transact-SQL)

sp_reinitmergepullsubscription (Transact-SQL)

sp_reinitmergesubscription (Transact-SQL)

sp_reinitpullsubscription (Transact-SQL)

sp_reinitsubscription (Transact-SQL)

sp_removedbreplication (Transact-SQL)

sp_removedistpublisherdbreplication (Transact-SQL)

sp_repladdcolumn (Transact-SQL)

sp_replcmds (Transact-SQL)

sp_replcounters (Transact-SQL)

sp_repldone (Transact-SQL)

sp_repldropcolumn (Transact-SQL)

sp_replflush (Transact-SQL)

sp_replication_agent_checkup (Transact-SQL)

sp_replicationdboption (Transact-SQL)

sp_replmonitorchangepublicationthreshold (Transact-SQL)

sp_replmonitorhelpmergesession (Transact-SQL)

sp_replmonitorhelpmergesessiondetail (Transact-SQL)

sp_replmonitorhelppublication (Transact-SQL)

sp_replmonitorhelppublicationthresholds (Transact-SQL)

sp_replmonitorhelppublisher (Transact-SQL)

sp_replmonitorhelpsubscription (Transact-SQL)

sp_replmonitorsubscriptionpendingcmds (Transact-SQL)

sp_replqueuemonitor (Transact-SQL)

sp_replrestart (Transact-SQL)

sp_replsetoriginator (Transact-SQL)

sp_replshowcmds (Transact-SQL)

sp_repltrans (Transact-SQL)

sp_requestpeerresponse (Transact-SQL)

sp_requestpeertopologyinfo (Transact-SQL)

sp_resetsnapshotdeliveryprogress (Transact-SQL)

sp_restoredbreplication (Transact-SQL)

sp_restoremergeidentityrange (Transact-SQL)

sp_resyncmergesubscription (Transact-SQL)

sp_revoke_publication_access (Transact-SQL)

sp_schemafilter (Transact-SQL)

sp_script_synctran_commands (Transact-SQL)

sp_scriptdynamicupdproc (Transact-SQL)

sp_scriptpublicationcustomprocs (Transact-SQL)

sp_scriptsubconflicttable (Transact-SQL)

sp_setdefaultdatatypemapping (Transact-SQL)

sp_setreplfailovermode (Transact-SQL)

sp_setsubscriptionxactseqno (Transact-SQL)

sp_showpendingchanges (Transact-SQL)

sp_showrowreplicainfo (Transact-SQL)

sp_startpublication_snapshot (Transact-SQL)

sp_subscription_cleanup (Transact-SQL)

sp_table_validation (Transact-SQL)

sp_unregister_custom_scripting (Transact-SQL)

sp_unregistercustomresolver (Transact-SQL)

sp_update_agent_profile (Transact-SQL)

sp_validate_redirected_publisher (Transact-SQL)

sp_validate_replica_hosts_as_publishers (Transact-SQL)

sp_validatemergepublication (Transact-SQL)

sp_validatemergesubscription (Transact-SQL)

sp_vupgrade_mergeobjects (Transact-SQL)

sp_vupgrade_replication (Transact-SQL)

Minimize

This topic has not yet been rated - Rate this topic

sp_setreplfailovermode (Transact-SQL)

SQL Server 2012

SQL Server 2005

Allows you to set the failover operation mode for subscriptions enabled for immediate updating with queued updating as failover. This stored procedure is executed at the Subscriber on the subscription database. For more information about failover modes, see Updatable Subscriptions for Transactional Replication.

Topic link icon Transact-SQL Syntax Conventions


          

sp_setreplfailovermode [ @publisher= ] 'publisher'
    [ , [ @publisher_db = ] 'publisher_db' ]
    [ , [ @publication= ] 'publication' ]
    [ , [ @failover_mode= ] 'failover_mode' ]
    [ , [ @override = ] override ]

[ @publisher=] 'publisher'

Is the name of the publication. publication is sysname, with no default. The publication must already exist.

[ @publisher_db =] 'publisher_db'

Is the name of the publication database. publisher_db is sysname, with no default.

[ @publication=] 'publication'

Is the name of the publication. publicationis sysname, with no default.

[@failover_mode=] 'failover_mode'

Is the failover mode for the subscription. failover_mode is nvarchar(10) and can be one of these values.

Value	Description
immediate or sync	Data modifications made at the Subscriber are bulk-copied to the Publisher as they occur.
queued	Data modifications are stored in a Microsoft SQL Server queue.

Note
Microsoft Message Queuing has been deprecated and is no longer supported.

[ @override= ] override

Internal use only.

Return Code Values

0 (success) or 1 (failure)

sp_setreplfailovermode is used in snapshot replication or transactional replication for which subscriptions are enabled, either for queued updating with failover to immediate updating, or for immediate updating with failover to queued updating.

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

Reference

System Stored Procedures (Transact-SQL)

Concepts

Switch Between Update Modes for an Updatable Transactional Subscription

Did you find this helpful? Yes No

Not accurate

Not enough depth

Need more code examples

(1500 characters remaining)

Community Additions

© 2013 Microsoft

Manage Your Profile

|

Site Feedback

© 2013 Microsoft. All rights reserved.