sp_add_agent_profile (Transact-SQL)
Collapse the table of content
Expand the table of content

sp_add_agent_profile (Transact-SQL)

Applies To: SQL Server 2014, SQL Server 2016 Preview

Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).

Creates a new profile for a replication agent. This stored procedure is executed at the Distributor on any database.

Topic link icon Transact-SQL Syntax Conventions

sp_add_agent_profile [ [ @profile_id= ] profile_id OUTPUT ]
        , [ @profile_name= ] 'profile_name'  
        , [ @agent_type= ] 'agent_type' ] 
    [ , [ @profile_type= ] profile_type ]
    [ , [ @description= ] 'description' ]
    [ , [ @default= ] default ]

[ @profile_id= ] profile_id

Is the ID associated with the newly inserted profile. profile_id is int and is an optional OUTPUT parameter. If specified, the value is set to the new profile ID.

[ @profile_name= ] 'profile_name'

Is the name of the profile. profile_name is sysname, with no default.

[ @agent_type= ] 'agent_type'

Is the type of replication agent. agent_type is int, with no default, and can be one of these values.




Snapshot Agent


Log Reader Agent


Distribution Agent


Merge Agent


Queue Reader Agent

[ @profile_type= ] profile_type

Is the type of profile.profile_type is int, with a default of 1.

0 indicates a system profile. 1 indicates a custom profile. Only custom profiles can be created using this stored procedure; therefore the only valid value is 1. Only Microsoft SQL Server creates system profiles.

[ @description= ] 'description'

Is a description of the profile. description is nvarchar(3000), with no default.

[ @default= ] default

Indicates whether the profile is the default for agent_type. default is bit, with a default of 0. 1 indicates that the profile being added will become the new default profile for the agent specified by agent_type.

0 (success) or 1 (failure)

sp_add_agent_profile is used in snapshot replication, transactional replication, and merge replication.

Custom agent profiles are added with the default agent parameter values. Use sp_change_agent_parameter (Transact-SQL) to change these default values or sp_add_agent_parameter (Transact-SQL) to add additional parameters.

When sp_add_agent_profile is executed, a row is added for the new custom profile in the MSagent_profiles (Transact-SQL) table and the associated default parameters for this profile are added to the MSagent_parameters (Transact-SQL) table.

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

Community Additions

© 2016 Microsoft