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?

1 Answer 1

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](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 ******/
1
  • 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. Mar 26, 2018 at 12:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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