April 25, 2012

sp_generate_agent_parameter (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_generate_agent_parameter(int @profile_id
, int @real_profile_id)

MetaData:

   
create procedure sys.sp_generate_agent_parameter(
@profile_id int, -- Fixed profile id used
@real_profile_id int = NULL -- Used by version upgrade
)
as
declare @retcode int

--
-- Security Check: require sysadmin
--
IF (ISNULL(IS_SRVROLEMEMBER('sysadmin'),0) = 0)
BEGIN
RAISERROR(21089,16,-1)
RETURN (1)
END

-- If real profile id is not passed in. Use the fix profile id.
if @real_profile_id is NULL
select @real_profile_id = @profile_id

--
-- Snapshot agent
--
if (@profile_id = 1)
begin
exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-BcpBatchSize',
@parameter_value = '100000'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-HistoryVerboseLevel',
@parameter_value = '2'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-LoginTimeout',
@parameter_value = '15'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-QueryTimeout',
@parameter_value = '1800' -- 30 minutes
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

end
--
-- Logreader agent
--
else if (@profile_id = 2)
begin

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-HistoryVerboseLevel',
@parameter_value = '1'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-LoginTimeout',
@parameter_value = '15'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-PollingInterval',
@parameter_value = '5'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-QueryTimeout',
@parameter_value = '1800' -- 30 minutes
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-ReadBatchSize',
@parameter_value = '500'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-LogScanThreshold',
@parameter_value = '500000'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

end

--
-- Logreader agent - Verbose History Profile
--
else if (@profile_id = 3)
begin

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-HistoryVerboseLevel',
@parameter_value = '2'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-LoginTimeout',
@parameter_value = '15'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-PollingInterval',
@parameter_value = '5'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-QueryTimeout',
@parameter_value = '1800' -- 30 minutes
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-ReadBatchSize',
@parameter_value = '500'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-LogScanThreshold',
@parameter_value = '500000'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE
end

--
-- Distribution agent
--
else if (@profile_id = 4)
begin

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-BcpBatchSize',
@parameter_value = '2147473647' -- Max 32bit signed integer - 10000
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-CommitBatchSize',
@parameter_value = '100'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-CommitBatchThreshold',
@parameter_value = '1000'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-HistoryVerboseLevel',
@parameter_value = '1'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-LoginTimeout',
@parameter_value = '15'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-MaxBcpThreads',
@parameter_value = '1'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-MaxDeliveredTransactions',
@parameter_value = '0'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-PollingInterval',
@parameter_value = '5'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-QueryTimeout',
@parameter_value = '1800' -- 30 minutes
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-TransactionsPerHistory',
@parameter_value = '100'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-SkipErrors',
@parameter_value = ''
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-KeepAliveMessageInterval',
@parameter_value = '300'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

end

--
-- Distribution Agent Verbose History Profile
--
else if (@profile_id = 5)
begin

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-BcpBatchSize',
@parameter_value = '2147473647'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-CommitBatchSize',
@parameter_value = '100'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-CommitBatchThreshold',
@parameter_value = '1000'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-HistoryVerboseLevel',
@parameter_value = '2'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-LoginTimeout',
@parameter_value = '15'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-MaxBcpThreads',
@parameter_value = '1'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-MaxDeliveredTransactions',
@parameter_value = '0'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-PollingInterval',
@parameter_value = '5'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-QueryTimeout',
@parameter_value = '1800' -- 30 minutes
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-TransactionsPerHistory',
@parameter_value = '100'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-SkipErrors',
@parameter_value = ''
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-KeepAliveMessageInterval',
@parameter_value = '300'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

end
--
-- Merge agent : Default profile for well connected scenarios
--
else if (@profile_id = 6)
begin
exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-PollingInterval',
@parameter_value = '60'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-StartQueueTimeout',
@parameter_value = '0'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-MaxDownloadChanges',
@parameter_value = '0'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-MaxUploadChanges',
@parameter_value = '0'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-ValidateInterval',
@parameter_value = '60'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-LoginTimeout',
@parameter_value = '15'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-QueryTimeout',
@parameter_value = '300'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-UploadGenerationsPerBatch',
@parameter_value = '50'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-DownloadGenerationsPerBatch',
@parameter_value = '50'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-UploadReadChangesPerBatch',
@parameter_value = '100'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-DownloadReadChangesPerBatch',
@parameter_value = '100'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-UploadWriteChangesPerBatch',
@parameter_value = '100'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-DownloadWriteChangesPerBatch',
@parameter_value = '100'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-Validate',
@parameter_value = '0'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-FastRowCount',
@parameter_value = '1'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-HistoryVerboseLevel',
@parameter_value = '2'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-ChangesPerHistory',
@parameter_value = '100'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-BcpBatchSize',
@parameter_value = '100000'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-NumDeadlockRetries',
@parameter_value = '5'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-SrcThreads',
@parameter_value = '2'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-DestThreads',
@parameter_value = '2'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-KeepAliveMessageInterval',
@parameter_value = '300'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-MetadataRetentionCleanup',
@parameter_value = '1'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-MaxBcpThreads',
@parameter_value = '2'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-MakeGenerationInterval',
@parameter_value = '1'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

end

--
-- Merge agent : Non default profile for disconnected scenarios ( unreliable link )
--
else if (@profile_id = 7)
begin

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-PollingInterval',
@parameter_value = '60'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-StartQueueTimeout',
@parameter_value = '0'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-MaxDownloadChanges',
@parameter_value = '0'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-MaxUploadChanges',
@parameter_value = '0'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-ValidateInterval',
@parameter_value = '60'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-LoginTimeout',
@parameter_value = '15'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-QueryTimeout',
@parameter_value = '300'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-UploadGenerationsPerBatch',
@parameter_value = '5'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-DownloadGenerationsPerBatch',
@parameter_value = '1'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-UploadReadChangesPerBatch',
@parameter_value = '100'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-DownloadReadChangesPerBatch',
@parameter_value = '100'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-UploadWriteChangesPerBatch',
@parameter_value = '100'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-DownloadWriteChangesPerBatch',
@parameter_value = '100'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-Validate',
@parameter_value = '0'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-FastRowCount',
@parameter_value = '1'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-HistoryVerboseLevel',
@parameter_value = '1'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-ChangesPerHistory',
@parameter_value = '100'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-BcpBatchSize',
@parameter_value = '100000'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-KeepAliveMessageInterval',
@parameter_value = '300'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-NumDeadlockRetries',
@parameter_value = '5'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-SrcThreads',
@parameter_value = '1'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-DestThreads',
@parameter_value = '1'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-MetadataRetentionCleanup',
@parameter_value = '1'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-MaxBcpThreads',
@parameter_value = '1'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-MakeGenerationInterval',
@parameter_value = '1'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE
end

--
-- Merge agent : Default profile for well connected scenarios - Verbose history logging
--
else if (@profile_id = 8)
begin
exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-PollingInterval',
@parameter_value = '60'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-StartQueueTimeout',
@parameter_value = '0'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-MaxDownloadChanges',
@parameter_value = '0'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-MaxUploadChanges',
@parameter_value = '0'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-ValidateInterval',
@parameter_value = '60'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-LoginTimeout',
@parameter_value = '15'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-QueryTimeout',
@parameter_value = '300'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-UploadGenerationsPerBatch',
@parameter_value = '50'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-DownloadGenerationsPerBatch',
@parameter_value = '50'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-UploadReadChangesPerBatch',
@parameter_value = '100'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-DownloadReadChangesPerBatch',
@parameter_value = '100'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-UploadWriteChangesPerBatch',
@parameter_value = '100'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-DownloadWriteChangesPerBatch',
@parameter_value = '100'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-Validate',
@parameter_value = '0'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-FastRowCount',
@parameter_value = '1'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-HistoryVerboseLevel',
@parameter_value = '3'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-ChangesPerHistory',
@parameter_value = '50'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-BcpBatchSize',
@parameter_value = '100000'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-NumDeadlockRetries',
@parameter_value = '5'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-SrcThreads',
@parameter_value = '2'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-DestThreads',
@parameter_value = '1'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-KeepAliveMessageInterval',
@parameter_value = '300'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-MetadataRetentionCleanup',
@parameter_value = '1'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-MaxBcpThreads',
@parameter_value = '2'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-MakeGenerationInterval',
@parameter_value = '1'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

end

--
-- Merge agent : (Synchronization Manager Profile)
--
else if (@profile_id = 9)
begin
exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-PollingInterval',
@parameter_value = '60'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-StartQueueTimeout',
@parameter_value = '0'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-MaxDownloadChanges',
@parameter_value = '0'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-MaxUploadChanges',
@parameter_value = '0'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-ValidateInterval',
@parameter_value = '60'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-LoginTimeout',
@parameter_value = '15'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-QueryTimeout',
@parameter_value = '300'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-UploadGenerationsPerBatch',
@parameter_value = '50'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-DownloadGenerationsPerBatch',
@parameter_value = '50'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-UploadReadChangesPerBatch',
@parameter_value = '100'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-DownloadReadChangesPerBatch',
@parameter_value = '100'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-UploadWriteChangesPerBatch',
@parameter_value = '100'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-DownloadWriteChangesPerBatch',
@parameter_value = '100'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-Validate',
@parameter_value = '0'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-FastRowCount',
@parameter_value = '1'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-HistoryVerboseLevel',
@parameter_value = '1'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-ChangesPerHistory',
@parameter_value = '50'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-BcpBatchSize',
@parameter_value = '1000'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-NumDeadlockRetries',
@parameter_value = '5'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-SrcThreads',
@parameter_value = '2'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-DestThreads',
@parameter_value = '1'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-KeepAliveMessageInterval',
@parameter_value = '300'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-MetadataRetentionCleanup',
@parameter_value = '1'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-MaxBcpThreads',
@parameter_value = '2'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-MakeGenerationInterval',
@parameter_value = '1'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE


end

--
-- Distribution agent (Synchronization Manager Profile)
--
else if (@profile_id = 10)
begin
exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-BcpBatchSize',
@parameter_value = '2147473647'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-CommitBatchSize',
@parameter_value = '100'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-CommitBatchThreshold',
@parameter_value = '1000'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-HistoryVerboseLevel',
@parameter_value = '1'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-LoginTimeout',
@parameter_value = '15'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-MaxBcpThreads',
@parameter_value = '1'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-MaxDeliveredTransactions',
@parameter_value = '0'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-PollingInterval',
@parameter_value = '5'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-QueryTimeout',
@parameter_value = '1800' -- 30 minutes
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-TransactionsPerHistory',
@parameter_value = '100'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-SkipErrors',
@parameter_value = ''
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-KeepAliveMessageInterval',
@parameter_value = '300'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

end

--
-- Queue Reader agent default profile
--
else if (@profile_id = 11)
begin
exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-HistoryVerboseLevel',
@parameter_value = '1'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-PollingInterval',
@parameter_value = '5'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-LoginTimeout',
@parameter_value = '15'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-QueryTimeout',
@parameter_value = '1800' -- 30 minutes
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE
end

--
-- Merge agent : Rowcount Validation profile for well connected scenarios
--
else if (@profile_id = 12)
begin
exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-PollingInterval',
@parameter_value = '60'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE


exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-StartQueueTimeout',
@parameter_value = '0'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-MaxDownloadChanges',
@parameter_value = '0'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-MaxUploadChanges',
@parameter_value = '0'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-ValidateInterval',
@parameter_value = '60'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-LoginTimeout',
@parameter_value = '15'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-QueryTimeout',
@parameter_value = '300'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-UploadGenerationsPerBatch',
@parameter_value = '50'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-DownloadGenerationsPerBatch',
@parameter_value = '50'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-UploadReadChangesPerBatch',
@parameter_value = '100'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-DownloadReadChangesPerBatch',
@parameter_value = '100'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-UploadWriteChangesPerBatch',
@parameter_value = '100'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-DownloadWriteChangesPerBatch',
@parameter_value = '100'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-Validate',
@parameter_value = '1'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-FastRowCount',
@parameter_value = '1'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-HistoryVerboseLevel',
@parameter_value = '1'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-ChangesPerHistory',
@parameter_value = '100'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE
exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-BcpBatchSize',
@parameter_value = '100000'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-NumDeadlockRetries',
@parameter_value = '5'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-SrcThreads',
@parameter_value = '2'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-DestThreads',
@parameter_value = '1'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-KeepAliveMessageInterval',
@parameter_value = '300'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-MetadataRetentionCleanup',
@parameter_value = '1'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-MaxBcpThreads',
@parameter_value = '2'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-MakeGenerationInterval',
@parameter_value = '1'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE
end

--
-- Merge agent : Rowcount & Checksum Validation profile for well connected scenarios
--
else if (@profile_id = 13)
begin
exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-PollingInterval',
@parameter_value = '60'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-StartQueueTimeout',
@parameter_value = '0'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-MaxDownloadChanges',
@parameter_value = '0'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-MaxUploadChanges',
@parameter_value = '0'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-ValidateInterval',
@parameter_value = '60'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-LoginTimeout',
@parameter_value = '15'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-QueryTimeout',
@parameter_value = '300'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-UploadGenerationsPerBatch',
@parameter_value = '50'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-DownloadGenerationsPerBatch',
@parameter_value = '50'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-UploadReadChangesPerBatch',
@parameter_value = '100'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-DownloadReadChangesPerBatch',
@parameter_value = '100'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-UploadWriteChangesPerBatch',
@parameter_value = '100'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-DownloadWriteChangesPerBatch',
@parameter_value = '100'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-Validate',
@parameter_value = '3'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-FastRowCount',
@parameter_value = '1'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-HistoryVerboseLevel',
@parameter_value = '2'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-ChangesPerHistory',
@parameter_value = '100'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE
exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-BcpBatchSize',
@parameter_value = '100000'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-NumDeadlockRetries',
@parameter_value = '5'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-SrcThreads',
@parameter_value = '2'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-DestThreads',
@parameter_value = '1'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-KeepAliveMessageInterval',
@parameter_value = '300'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-MetadataRetentionCleanup',
@parameter_value = '1'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-MaxBcpThreads',
@parameter_value = '2'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-MakeGenerationInterval',
@parameter_value = '1'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

end

--
-- Distribution agent, skip error profile
--
else if (@profile_id = 14)
begin

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-BcpBatchSize',
@parameter_value = '2147473647'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-CommitBatchSize',
@parameter_value = '100'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-CommitBatchThreshold',
@parameter_value = '1000'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-HistoryVerboseLevel',
@parameter_value = '1'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-LoginTimeout',
@parameter_value = '15'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-MaxBcpThreads',
@parameter_value = '1'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-MaxDeliveredTransactions',
@parameter_value = '0'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-PollingInterval',
@parameter_value = '5'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-QueryTimeout',
@parameter_value = '1800' -- 30 minutes
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-TransactionsPerHistory',
@parameter_value = '100'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-SkipErrors',
@parameter_value = '2601:2627:20598'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-KeepAliveMessageInterval',
@parameter_value = '300'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

end

--
-- Merge agent : Rowcount Validation profile for high volumn server-to-server
--
else if (@profile_id = 15)
begin
exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-PollingInterval',
@parameter_value = '60'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-StartQueueTimeout',
@parameter_value = '0'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-MaxDownloadChanges',
@parameter_value = '0'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-MaxUploadChanges',
@parameter_value = '0'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-ValidateInterval',
@parameter_value = '60'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-LoginTimeout',
@parameter_value = '15'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-QueryTimeout',
@parameter_value = '600'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-UploadGenerationsPerBatch',
@parameter_value = '500'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-DownloadGenerationsPerBatch',
@parameter_value = '500'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-UploadReadChangesPerBatch',
@parameter_value = '100'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-DownloadReadChangesPerBatch',
@parameter_value = '100'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-UploadWriteChangesPerBatch',
@parameter_value = '100'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-DownloadWriteChangesPerBatch',
@parameter_value = '100'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-Validate',
@parameter_value = '0'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-FastRowCount',
@parameter_value = '1'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-HistoryVerboseLevel',
@parameter_value = '2'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-ChangesPerHistory',
@parameter_value = '1000'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-BcpBatchSize',
@parameter_value = '100000'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-NumDeadlockRetries',
@parameter_value = '5'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-SrcThreads',
@parameter_value = '3'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-DestThreads',
@parameter_value = '4'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-KeepAliveMessageInterval',
@parameter_value = '300'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-MetadataRetentionCleanup',
@parameter_value = '1'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-ParallelUploadDownload',
@parameter_value = '1'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-QueueSizeMultiplier',
@parameter_value = '5'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-MaxBcpThreads',
@parameter_value = '4'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-MakeGenerationInterval',
@parameter_value = '1'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

end
else if (@profile_id = 16)
begin
--
-- Distribution agent - profile to use OLEDB streaming for LOB data
--
exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-BcpBatchSize',
@parameter_value = '2147473647' -- Max 32bit signed integer - 10000
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-CommitBatchSize',
@parameter_value = '100'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-CommitBatchThreshold',
@parameter_value = '1000'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-HistoryVerboseLevel',
@parameter_value = '1'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-LoginTimeout',
@parameter_value = '15'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-MaxBcpThreads',
@parameter_value = '1'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-MaxDeliveredTransactions',
@parameter_value = '0'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-PollingInterval',
@parameter_value = '5'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-QueryTimeout',
@parameter_value = '1800' -- 30 minutes
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-TransactionsPerHistory',
@parameter_value = '100'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-SkipErrors',
@parameter_value = ''
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-KeepAliveMessageInterval',
@parameter_value = '300'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-UseOledbStreaming',
@parameter_value = ''
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-OledbStreamThreshold',
@parameter_value = '32768'
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE

exec @retcode = sys.sp_add_agent_parameter
@profile_id = @real_profile_id,
@parameter_name = '-PacketSize',
@parameter_value = '32768' -- use a bigger packet size
if (@retcode = 1 or @@ERROR <> 0)
goto FAILURE
end

return 0
FAILURE:
return 1

No comments:

Post a Comment

Total Pageviews