0

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?

0

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;

--— 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) + ''';'

EXEC (@SQL)

GO

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

  • Sorry but I don't understand the benefit of dropping one and recreating that. I need to generate the script to migrate that. Thanks for your response. – Rafael Dontal Gonçalez Mar 26 '18 at 12:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.