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 2005

Product Documentation

SQL Server 2005 Documentation

SQL Server 2005 Books Online

SQL Server Language Reference

Transact-SQL Reference

System Stored Procedures (Transact-SQL)

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_copymergesnapshot (Transact-SQL)

sp_copysnapshot (Transact-SQL)

sp_copysubscription (Transact-SQL)

sp_deletemergeconflictrow (Transact-SQL)

sp_deletepeerrequesthistory (Transact-SQL)

sp_deletetracertokenhistory (Transact-SQL)

sp_disableagentoffload (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_enableagentoffload (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_getagentparameterlist (Transact-SQL)

sp_getdefaultdatatypemapping (Transact-SQL)

sp_getmergedeletetype (Transact-SQL)

sp_get_distributor (Transact-SQL)

sp_getqueuedrows (Transact-SQL)

sp_getsubscriptiondtspackagename (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_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_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_replsetoriginator (Transact-SQL)

sp_replshowcmds (Transact-SQL)

sp_repltrans (Transact-SQL)

sp_requestpeerresponse (Transact-SQL)

sp_resetsnapshotdeliveryprogress (Transact-SQL)

sp_replrestart (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_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_changedistpublisher (Transact-SQL)

SQL Server 2005

Changes the properties of the distribution Publisher. This stored procedure is executed at the Distributor on any database.

Topic link icon Transact-SQL Syntax Conventions


sp_changedistpublisher [ @publisher = ] 'publisher'
    [ , [ @property = ] 'property' ]
    [ , [ @value = ] 'value' ]

[ @publisher = ] 'publisher': Is the Publisher name. publisher is sysname, with no default.

[ @property = ] 'property': Is a property to change for the given Publisher. property is sysname and can be one of these values.

[ @value = ] 'value'

Is the value for the given property. value is nvarchar(255), with a default of NULL.

This table describes the properties of Publishers and the values for those properties.

Property	Values	Description
active	true	Activates the Publisher.
	false	Deactivates the publisher
distribution_db		Name of the distribution database.
login		Login name.
password		Strong password for the supplied login.
security_mode	1	Use Windows Authentication when connecting to the Publisher. This cannot be changed for a non-Microsoft SQL Server publisher.
	0	Use SQL Server Authentication when connecting to the Publisher. This cannot be changed for a non-SQL Server publisher.
working_directory		Working directory used to store data and schema files for the publication.
NULL (default)		All available property options are printed.

Return Code Values

0 (success) or 1 (failure)

sp_changedistpublisher is used in all types of replication.

Only members of the sysadmin fixed server role can execute sp_changedistpublisher.

Reference

sp_adddistpublisher (Transact-SQL)
sp_dropdistpublisher (Transact-SQL)
sp_helpdistpublisher (Transact-SQL)
System Stored Procedures (Transact-SQL)

Other Resources

How to: View and Modify Publisher and Distributor Properties (Replication Transact-SQL Programming)

Help and Information

Getting SQL Server 2005 Assistance

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.