June 11, 2012

sp_syspolicy_update_event_notification (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_update_event_notification()

MetaData:

 CREATE PROCEDURE sys.sp_syspolicy_update_event_notification   
AS
IF (msdb.dbo.fn_syspolicy_is_automation_enabled() = 0)
BEGIN
RETURN;
END

DECLARE @event_list nvarchar(max)
SET @event_list = N''

CREATE TABLE #temp_events (event_name sysname COLLATE database_default)
INSERT #temp_events
SELECT DISTINCT fe.event_name COLLATE database_default
FROM msdb.dbo.syspolicy_policies pi
INNER JOIN msdb.dbo.syspolicy_conditions_internal c ON pi.condition_id = c.condition_id
INNER JOIN msdb.dbo.syspolicy_facet_events fe ON c.facet_id = fe.management_facet_id
INNER JOIN sys.event_notification_event_types ent ON fe.event_name = ent.type_name COLLATE database_default
WHERE (pi.execution_mode & 2) = 2 AND pi.is_enabled = 1 AND
EXISTS (SELECT * FROM msdb.dbo.syspolicy_object_sets_internal os WHERE os.object_set_id = pi.object_set_id)

DECLARE event_cursor CURSOR LOCAL FOR
SELECT event_name FROM #temp_events

DECLARE @event_name sysname
OPEN event_cursor
FETCH NEXT FROM event_cursor INTO @event_name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @event_list = @event_list + @event_name + ','
FETCH NEXT FROM event_cursor INTO @event_name
END

CLOSE event_cursor
DEALLOCATE event_cursor

DROP TABLE #temp_events

-- take out the last comma
IF( LEN(@event_list) > 0)
SET @event_list = SUBSTRING(@event_list, 1, LEN(@event_list)-1)

-- we need to drop and recreate the notification, because
-- it cannot be altered
BEGIN TRANSACTION

IF EXISTS (SELECT * FROM sys.server_event_notifications WHERE name = N'syspolicy_event_notification')
DROP EVENT NOTIFICATION [syspolicy_event_notification] ON SERVER

-- no more events to read means we only drop the notification
IF(LEN(@event_list)=0)
BEGIN
COMMIT TRANSACTION
RETURN (0)
END

DECLARE @statement nvarchar(max)
SET @statement = N'USE msdb; CREATE EVENT NOTIFICATION [syspolicy_event_notification] ON SERVER
FOR '
+ @event_list + '
TO SERVICE '
'syspolicy_event_listener'', ''current database'';'
EXEC sp_executesql @statement

COMMIT TRANSACTION

RETURN (0)

No comments:

Post a Comment

Total Pageviews