Share via

sp_change_agent_parameter (Transact-SQL)

  • Article

Changes a parameter of a replication agent profile stored in the MSagent_parameters system table. This stored procedure is executed at the Distributor where the agent is running, on any database.

Topic link iconTransact-SQL Syntax Conventions

Syntax

sp_change_agent_parameter [ @profile_id= ] profile_id, [ @parameter_name= ] 'parameter_name', [ @parameter_value= ] 'parameter_value'

Arguments

  • [ @profile_id=] profile_id,
    Is the ID of the profile. profile_id is int, with no default.

  • [ @parameter_name=] 'parameter_name'
    Is the name of the parameter. parameter_name is sysname, with no default. For system profiles, the parameters that can be changed depend on the type of agent. To find out what type of agent this profile_id represents, locate the profile_id column in the Msagent_profiles table, and note the agent_type value.

    Note

    If a parameter is supported for a given agent_type, but has not been defined in the agent profile, an error is returned. To add a parameter to an agent profile you must execute sp_add_agent_parameter.

    For a Snapshot Agent (agent_type=1), if defined in the profile, the following properties can be changed:

    • 70Subscribers
    • BcpBatchSize
    • HistoryVerboseLevel
    • LoginTimeout
    • MaxBcpThreads
    • MaxNetworkOptimization
    • Output
    • OutputVerboseLevel
    • PacketSize
    • QueryTimeout
    • StartQueueTimeout
    • UsePerArticleContentsView

    For a Log Reader Agent (agent_type=2), if defined in the profile, the following properties can be changed:

    • HistoryVerboseLevel
    • LoginTimeout
    • MessageInterval
    • Output
    • OutputVerboseLevel
    • PacketSize
    • PollingInterval
    • QueryTimeout
    • ReadBatchSize
    • ReadBatchThreshold

    For a Distribution Agent (agent_type=3), if defined in the profile, the following properties can be changed:

    • BcpBatchSize
    • CommitBatchSize
    • CommitBatchThreshold
    • FileTransferType
    • HistoryVerboseLevel
    • KeepAliveMessageInterval
    • LoginTimeout
    • MaxBcpThreads
    • MaxDeliveredTransactions
    • MessageInterval
    • Output
    • OutputVerboseLevel
    • PacketSize
    • PollingInterval
    • QueryTimeout
    • QuotedIdentifier
    • SkipErrors
    • TransactionsPerHistory

    For a Merge Agent (agent_type=4), if defined in the profile, the following properties can be changed:

    • AltSnapshotFolder
    • BcpBatchSize
    • ChangesPerHistory
    • DestThreads
    • DownloadGenerationsPerBatch
    • DownloadReadChangesPerBatch
    • DownloadWriteChangesPerBatch
    • DynamicSnapshotLocation
    • ExchangeType
    • FastRowCount
    • FileTransferType
    • GenerationChangeThreshold
    • HistoryVerboseLevel
    • InputMessageFile
    • InteractiveResolution
    • InterruptOnMessagePattern
    • KeepAliveMessageInterval
    • LoginTimeout
    • MaxBcpThreads
    • MaxDownloadChanges
    • MaxUploadChanges
    • MetadataRetentionCleanup
    • NumDeadlockRetries
    • Output
    • OutputMessageFile
    • OutputVerboseLevel
    • PacketSize
    • ParallelUploadDownload
    • PauseOnMessagePattern
    • PauseTime
    • PollingInterval
    • ProcessMessagesAtPublisher
    • ProcessMessagesAtSubscriber
    • QueryTimeout
    • QueueSizeMultiplier
    • SrcThreads
    • StartQueueTimeout
    • SyncToAlternate
    • UploadGenerationsPerBatch
    • UploadReadChangesPerBatch
    • UploadWriteChangesPerBatch
    • UseInprocLoader
    • Validate
    • ValidateInterval

    For a Queue Reader Agent (agent_type=9), if defined in the profile, the following properties can be changed:

    • HistoryVerboseLevel
    • LoginTimeout
    • Output
    • OutputVerboseLevel
    • PollingInterval
    • QueryTimeout
    • ResolverState
    • SQLQueueMode

    To see what parameters have been defined for a given profile, run sp_help_agent_profile and note the profile_name associated with the profile_id. With the appropriate profile_id, next run sp_help_agent_parameters using that profile_id to see the parameters associated with the profile. Parameters can be added to a profile by executing sp_add_agent_parameter.

  • [ @parameter_value=] 'parameter_value'
    Is the new value of the parameter. parameter_value is nvarchar(255), with no default.

Return Code Values

0 (success) or 1 (failure)

Remarks

sp_change_agent_parameter is used in all types of replication.

Permissions

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

See Also

Reference

sp_add_agent_parameter (Transact-SQL)
sp_drop_agent_parameter (Transact-SQL)
sp_help_agent_parameter (Transact-SQL)
System Stored Procedures (Transact-SQL)

Other Resources

Replication Agent Profiles
Replication Distribution Agent
Replication Log Reader Agent
Replication Merge Agent
Replication Queue Reader Agent
Replication Snapshot Agent

Help and Information

Getting SQL Server 2005 Assistance