April 13, 2012

sp_add_agent_profile (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_add_agent_profile(nvarchar @profile_name
, int @agent_type
, int @profile_type
, nvarchar @description
, bit @default)

MetaData:

   
--
-- The system profile of the same type of agent will be used as a template for
-- the parameters in this new user profile.
--
create procedure sys.sp_add_agent_profile (
@profile_id int = NULL OUTPUT,
@profile_name sysname,
@agent_type int, -- 1-Snapshot, 2-Logreader,
-- 3-Distribution, 4-Merge,
-- 9-Qreader
@profile_type int = 1, -- 0-System, 1-Custom
@description nvarchar(3000) = NULL,
@default bit = 0 -- 0-Not Default, 1-Default
)
AS
SET NOCOUNT ON

declare @sys_profile int
declare @default_id int
declare @sysdefault_id int
declare @retcode int

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

SELECT @profile_name = RTRIM(@profile_name)

exec @retcode = sys.sp_MSreplcheck_name @profile_name, '@profile_name', 'sp_add_agent_profile'
if @@ERROR <> 0 or @retcode <> 0
return(1)

-- The profile name is unique across a particular agent type --
IF EXISTS ( SELECT * FROM msdb..MSagent_profiles
WHERE profile_name = @profile_name collate database_default
AND agent_type = @agent_type )
BEGIN
RAISERROR(20057, 16, -1, @profile_name)
RETURN (1)
END

IF @agent_type NOT IN (1, 2, 3, 4, 9)
BEGIN
RAISERROR(20058, 16, -1)
return (1)
END

IF @profile_type NOT IN (0, 1)
BEGIN
RAISERROR(20059, 16, -1)
return (1)
END

BEGIN TRAN

--
-- First find out the default profile of the same agent type
--
select @default_id = profile_id from msdb..MSagent_profiles WHERE agent_type = @agent_type AND def_profile = 1

INSERT INTO msdb..MSagent_profiles
VALUES (@profile_name, @agent_type, @profile_type, @description, 0)
IF @@ERROR <> 0
GOTO UNDO

SELECT @profile_id = profile_id
FROM msdb..MSagent_profiles
WHERE profile_name = @profile_name collate database_default AND agent_type = @agent_type

--
-- If there is system profiles of the same agent type, use the primary system one as template of parameter set
--
IF EXISTS (select * from msdb..MSagent_profiles where agent_type = @agent_type AND type = 0)
select @sysdefault_id = min(profile_id) from msdb..MSagent_profiles where agent_type = @agent_type AND type = 0

if @sysdefault_id IS NULL
select @sysdefault_id = @default_id

if @profile_type=1
BEGIN
INSERT INTO msdb..MSagent_parameters
select @profile_id, parameter_name, value from msdb..MSagent_parameters
where profile_id = @sysdefault_id
if @@ERROR<>0
GOTO UNDO
END

-- Only one profile for an agent type must be default --
IF @default = 1
BEGIN
UPDATE msdb..MSagent_profiles
SET def_profile = 0 WHERE profile_id = @default_id
IF @@ERROR <> 0
GOTO UNDO
UPDATE msdb..MSagent_profiles
SET def_profile = 1 WHERE profile_id = @profile_id
IF @@ERROR <> 0
GOTO UNDO
END

COMMIT TRAN
RETURN 0

UNDO:

IF @@TRANCOUNT = 1
ROLLBACK TRAN
ELSE
COMMIT TRAN

RETURN 1

No comments:

Post a Comment

Total Pageviews