Export (0) Print
Expand All
Expand Minimize

sp_MSchange_distribution_agent_properties (Transact-SQL)

Changes the properties of a Distribution Agent job that runs at a Microsoft SQL Server 2005 or later version Distributor. This stored procedure is used to change properties when the Publisher runs on an instance of SQL Server 2000. This stored procedure is executed at the Distributor on the distribution database.

Topic link icon Transact-SQL Syntax Conventions


          

sp_MSchange_distribution_agent_properties [ @publisher = ] 'publisher'
        , [ @publisher_db = ] 'publisher_db'
        , [ @publication = ] 'publication' 
        , [ @subscriber = ] 'subscriber' 
        , [ @subscriber_db = ] 'subscriber_db' 
        , [ @property = ] 'property' 
        , [ @value = ] 'value' ]
        

[ @publisher = ] 'publisher'

Is the name of the Publisher. publisher is sysname, with no default.

[ @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. publication is sysname, with no default.

[ @subscriber= ] 'subscriber'

Is the name of the Subscriber. subscriber is sysname, with no default.

[ @subscriber_db= ] 'subscriber_db'

Is the name of the subscription database. subscriber_db is sysname, with no default.

[ @property = ] 'property'

Is the publication property to change. property is sysname, with no default.

[ @value = ] 'value'

Is the new property value. value is nvarchar(524), with a default of NULL.

This table describes the properties of the Distribution Agent job that can be changed, and restrictions on the values for those properties.

Property

Value

Description

distrib_job_login

Login for the Microsoft Windows account under which the agent runs.

distrib_job_password

Password for the Windows account under which the agent job runs.

subscriber_catalog

Catalog to be used when making a connection to the OLE DB provider. This property is only valid for non-SQL Server Subscribers. 

subscriber_datasource

Name of the data source as understood by the OLE DB provider. This property is only valid for non-SQL Server Subscribers.

subscriber_location

Location of the database as understood by the OLE DB provider. This property is only valid for non-SQL Server Subscribers.

subscriber_login

Login to use when connecting to a Subscriber to synchronize the subscription.

subscriber_password

Subscriber password.

Do not use a blank password. Use a strong password.

subscriber_provider

Unique 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_providerstring

OLE DB provider-specific connection string that identifies the data source. This property is only valid for non-SQL Server Subscribers.

subscriber_security_mode

1

Windows Authentication.

When possible, use Windows Authentication.

0

SQL Server Authentication.

subscriber_type

0

SQL Server Subscriber

1

ODBC data source server

3

OLE DB provider

subscriptionstreams

Denotes the number of connections allowed per Distribution Agent to apply batches of changes in parallel to a Subscriber. Not supported for non-SQL Server Subscribers, Oracle Publishers, or peer-to-peer subscriptions.

Note Note

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

0 (success) or 1 (failure)

sp_MSchange_distribution_agent_properties is used in snapshot replication and transactional replication.

When the Publisher runs on an instance of SQL Server 2005 or later version, you should use sp_changesubscription to change properties of a Merge Agent job that synchronizes a push subscription that runs at the Distributor.

Only members of the sysadmin fixed server role at the Distributor can execute sp_MSchange_distribution_agent_properties.

Community Additions

ADD
Show:
© 2014 Microsoft