Sql-server – Generate Script for Event Notification

eventevent-notificationsql server

I need to migrate my Event Notifications from current server to another one. I would like to generate that code, but I couldn't find an way to do that.

Try 1

I have tried reverse engineering from sys.server_event_notifications but it does not return a value that I can marry with sys.event_notification_event_typeid.

Try 2

I tried also a DAC to query system views, getting more columns from that, but no joy.

Try 3

Tried sp_helptext the objects, but it can't be found.

EXPECTED OUTPUT (for one event)

CREATE EVENT NOTIFICATION [CaptureDeadlocks]
ON SERVER
WITH FAN_IN
FOR DEADLOCK_GRAPH
TO SERVICE N'DeadlockService', N'current database';

Any ideas?

Best Answer

I am unaware of a way to script the existing one out so I drop and create a new one each time. The following script assumes a database name of DBA and a Service of DeadlockEventsService (viewable in SSMS under Service Broker Services)

/****** BEGIN Create the Event Notification object that will capture the 

deadlocks ******/
USE [DBA]
GO

EXECUTE AS LOGIN = 'sa';
DECLARE @AuditServiceBrokerGuid [uniqueidentifier]
,@SQL [varchar](max);

--— Retrieving the service broker guid of CaptureDeadlockGraph database

SELECT @AuditServiceBrokerGuid = [service_broker_guid]
FROM [master].[sys].[databases]
WHERE [name] = 'DBA'

--— Building and executing dynamic SQL to create event notification object

SET @SQL = 'IF EXISTS (SELECT * FROM sys.server_event_notifications 
WHERE name = ''DeadlockGraphEventNotification'')

DROP EVENT NOTIFICATION DeadlockGraphEventNotification ON SERVER

CREATE EVENT NOTIFICATION DeadlockGraphEventNotification 
ON SERVER
WITH FAN_IN
FOR DEADLOCK_GRAPH
TO SERVICE ''DeadlockEventsService'', '''
+ CAST(@AuditServiceBrokerGuid AS [varchar](50)) + ''';'

EXEC (@SQL)

GO

/****** END Create the Event Notification object that will capture the deadlocks ******/
Related Question