sp_change_agent_parameter (Transact-SQL)

Artykuł
02/13/2015

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.

Ikona łącza do tematu Transact-SQL Syntax Conventions

Składnia

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.

[!UWAGA]

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.