June 11, 2012

sp_syspolicy_execute_policy (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_syspolicy_execute_policy(nvarchar @policy_name
, xml @event_data
, bit @synchronous)

MetaData:

 CREATE PROCEDURE sys.sp_syspolicy_execute_policy   
@policy_name sysname,
@event_data xml,
@synchronous bit = 0
AS
BEGIN

IF(DB_NAME() != 'msdb')
BEGIN
RAISERROR(14416,-1,-1)
RETURN 14416
END

-- make sure that the caller is dbo or PolicyAdministratorRole
IF ( IS_MEMBER('PolicyAdministratorRole') != 1 AND UPPER(USER_NAME())!= 'DBO')
BEGIN
RAISERROR(15003, -1, -1, 'PolicyAdministratorRole')
RETURN 15003
END

DECLARE @retval int
DECLARE @history_id bigint
SET @history_id = 0

EXEC @retval = sys.sp_execute_policy @policy_name, @event_data, @history_id OUTPUT

IF( (@synchronous = 1) AND (@retval=1))
BEGIN

-- the policy failed so we are going to abort the transaction

-- make sure we get the log information so that we can recreate
-- the log entry
DECLARE @policy_id int
DECLARE @start_date datetime
DECLARE @end_date datetime
DECLARE @result bit

SELECT @policy_id = policy_id,
@start_date = start_date,
@end_date = end_date,
@result = result
FROM msdb.dbo.syspolicy_policy_execution_history_internal
WHERE history_id = @history_id

DECLARE @target_query_expression nvarchar(max)
DECLARE @execution_date datetime
DECLARE @target_result bit
DECLARE @result_detail nvarchar(max)

-- there is only one target
SELECT TOP 1 @target_query_expression = target_query_expression,
@execution_date = execution_date,
@target_result = result,
@result_detail = result_detail
FROM msdb.dbo.syspolicy_policy_execution_history_details_internal
WHERE history_id = @history_id

ROLLBACK TRANSACTION

IF( @policy_id IS NOT NULL)
BEGIN
SET IDENTITY_INSERT msdb.dbo.syspolicy_policy_execution_history_internal ON
INSERT INTO msdb.dbo.syspolicy_policy_execution_history_internal(history_id, policy_id, start_date, end_date, result)
VALUES (@history_id, @policy_id, @start_date, @end_date, @result)
SET IDENTITY_INSERT msdb.dbo.syspolicy_policy_execution_history_internal OFF
-- insert empty string because the expression is no longer valid
-- once the transaction has been rolled back
INSERT INTO msdb.dbo.syspolicy_policy_execution_history_details_internal (history_id, target_query_expression, target_query_expression_with_id, execution_date, result, result_detail)
VALUES (@history_id, @target_query_expression, N'', @execution_date, @target_result, @result_detail)
END
END

RETURN @retval
END

No comments:

Post a Comment

Total Pageviews