Get information about event

Question

Hi, ALL,

The docs at https://learn.microsoft.com/en-us/sql/t-sql/statements/create-event-notification-transact-sql?view=sql-server-ver16#examples sample D shows how check for the event

However, I took a look at the sys.event_notification table and there is nothing that allows to know what table has been modified.

Is there a way to know this?

Thank you.

EDIT:

This is what I'm doing:

IF EXISTS(SELECT * FROM sys.databases WHERE name = '" + pimpl.m_dbName + L"' AND is_broker_enabled = 0) ALTER DATABASE " + pimpl.m_dbName + L" SET ENABLE_BROKER"

IF NOT EXISTS(SELECT * FROM sys.service_queues WHERE name = 'EventNotificationQueue') CREATE QUEUE dbo.EventNotificationQueue

IF NOT EXISTS(SELECT * FROM sys.services WHERE name = '//" + pimpl.m_dbName + L"/EventNotificationService') CREATE SERVICE [//" + pimpl.m_dbName +L"/EventNotificationService] ON QUEUE dbo.EventNotificationQueue([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification])

"IF NOT EXISTS(SELECT * FROM sys.event_notifications WHERE name = 'SchemaChangeEventsTable') CREATE EVENT NOTIFICATION SchemaChangeEventsTable ON DATABASE FOR DDL_TABLE_EVENTS TO SERVICE '//" + pimpl.m_dbName + L"/EventNotificationService' , 'current database'"

Answer 1

Hi @Igor Korot

Event Notifications do not automatically capture the details information of specific tables or columns affected.

To track which specific table is being modified, you could try change data capture (CDC).

Best regards,

Cosmog

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 2

Well, you need to look in the queue:

SELECT convert(xml, message_body)
FROM EventNotificationQueue

Although, you would only use the SELECT command to peek into the queue. Normally, you would use the RECEIVE command to take it off the queue when you process the notification.