sp_changesubscription (Transact-SQL)
TOC
Collapse the table of content
Expand the table of content

sp_changesubscription (Transact-SQL)

 

Updated: October 28, 2015

Applies To: SQL Server

Changes the properties of a snapshot or transactional push subscription or a pull subscription involved in queued updating transactional replication. To change properties of all other types of pull subscriptions, use sp_change_subscription_properties (Transact-SQL). sp_changesubscription is executed at the Publisher on the publication database.

System_CAPS_ICON_important.jpg Important


When configuring a Publisher with a remote Distributor, the values supplied for all parameters, including job_login and job_password, are sent to the Distributor as plain text. You should encrypt the connection between the Publisher and its remote Distributor before executing this stored procedure. For more information, see Enable Encrypted Connections to the Database Engine (SQL Server Configuration Manager).

Topic link icon Transact-SQL Syntax Conventions

  
sp_changesubscription [ @publication = ] 'publication'  
        , [ @article = ] 'article'  
        , [ @subscriber = ] 'subscriber'  
        , [ @destination_db = ] 'destination_db'  
        , [ @property = ] 'property'  
        , [ @value = ] 'value'  
    [ , [ @publisher = ] 'publisher' ]  

[ @publication= ] 'publication'
Is the name of the publication to change. publicationis sysname, with no default

[ @article = ] 'article'
Is the name of the article to change. article is sysname, with no default.

[ @subscriber = ] 'subscriber'
Is the name of the Subscriber. subscriber is sysname, with no default.

[ @destination_db = ] 'destination_db'
Is the name of the subscription database. destination_db is sysname, with no default.

[ @property=] 'property'
Is the property to change for the given subscription. property is nvarchar(30), and can be one of the values in the table.

[ @value=] 'value'
Is the new value for the specified property. value is nvarchar(4000), and can be one of the values in the table.

PropertyValueDescription
distrib_job_loginLogin for the Microsoft Windows account under which the agent runs.
distrib_job_passwordPassword for the Windows account under which the agent runs.
subscriber_catalogCatalog to be used when making a connection to the OLE DB provider. This property is only valid for non-Microsoft SQL Server Subscribers.
subscriber_datasourceName of the data source as understood by the OLE DB provider. This property is only valid for non-SQL Server Subscribers.
subscriber_locationLocation of the database as understood by the OLE DB provider. This property is only valid for non-SQL Server Subscribers.
subscriber_loginLogin name at the Subscriber.
subscriber_passwordStrong password for the supplied login.
subscriber_security_mode1Use Windows Authentication when connecting to the Subscriber.
0Use SQL Server Authentication when connecting to the Subscriber.
subscriber_providerUnique programmatic identifier (PROGID) with which the OLE DB provider for the non-SQL Server data source is registered. This property is only valid for non-SQL Server Subscribers.
subscriber_providerstringOLE DB provider-specific connection string that identifies the data source. This property is only valid for non-SQL Server Subscribers.
subscriptionstreamsIs the number of connections allowed per Distribution Agent to apply batches of changes in parallel to a Subscriber. A range of values from 1 to 64 is supported for SQL Server Publishers. This property must be 0 for non-SQL Server Subscribers, Oracle Publishers or peer-to-peer subscriptions.
subscriber_type1ODBC data source server
3OLE DB provider
memory_optimizedbitIndicates that the subscription supports memory optimized tables. memory_optimized is bit, where 1 equals true (the subscription supports memory optimized tables).

[ @publisher = ] 'publisher'
Specifies a non-SQL Server Publisher. publisher is sysname, with a default of NULL.

System_CAPS_ICON_note.jpg Note


publisher should not be specified for a SQL Server Publisher.

0 (success) or 1 (failure)

sp_changesubscription is used in snapshot and transactional replication.

sp_changesubscription can only be used to modify the properties of push subscriptions or pull subscriptions involved in queued updating transactional replication. To change properties of all other types of pull subscriptions, use sp_change_subscription_properties (Transact-SQL).

After changing an agent login or password, you must stop and restart the agent before the change takes effect.

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

sp_addsubscription (Transact-SQL)
sp_dropsubscription (Transact-SQL)

Community Additions

ADD
Show:
© 2016 Microsoft