sp_change_agent_parameter (Transact-SQL)
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.
.gif "Topic link icon")
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