Can't seem to get Notification fired

Sunday, May 20, 2012 7:23 PM

I am trying to catch errors, exceptions (like database have no space etc) and then link them to the specific session, get the sql statements etc and generate an email, I figured I'd use event notification for it. I already have an event notification for deadlocks working properly on this server. But this one seems simple to setup but for some reason it's not firing, I have profiler open on the side monitoring errors, exceptions, eventlog events and trying to catch them with the even notification but no event is firing, and I am catching the events in the profiler no problem. Am I missing some configuration, this is sql 2012 (and also sql 2008 sp2) both 64 bit.

Thank you

USE [DBA];
GO
EXEC SP_CHANGEDBOWNER 'SA'
GO
ALTER DATABASE DBA SET TRUSTWORTHY ON
GO
ALTER DATABASE [DBA] SET NEW_BROKER WITH ROLLBACK IMMEDIATE
GO
--  Cleanup objects
DROP EVENT NOTIFICATION CaptureException ON SERVER
GO
DROP ROUTE ExceptionRoute
GO
DROP SERVICE ExceptionService
GO
DROP QUEUE ExceptionQueue
GO
DROP PROCEDURE [dbo].[ProcessException]
GO
DROP TABLE TestExceptions
GO
CREATE TABLE TestExceptions (id INT IDENTITY PRIMARY KEY CLUSTERED, DATA XML)
GO
IF OBJECT_ID('[dbo].[ProcessException]') IS NULL 
    EXEC('CREATE PROCEDURE [dbo].[ProcessException] AS RETURN')
GO
ALTER PROCEDURE [dbo].[ProcessException]
AS 
SET NOCOUNT ON
DECLARE @message_body XML
  , @message_sequence_number INT
  , @email_message NVARCHAR(MAX)
  , @Body NVARCHAR(MAX)
  , @Subject NVARCHAR(2000)
WHILE (1 = 1) 
    BEGIN
-- Receive the next available message FROM the queue

        WAITFOR (
		RECEIVE TOP(1) -- just handle one message at a time
			@message_body = CAST(message_body AS XML),
			@message_sequence_number=message_sequence_number
			FROM dbo.ExceptionQueue
	), TIMEOUT 1000  -- if the queue is empty for one second, give UPDATE AND GO away

-- If we didn't get anything, bail out
        IF (@@ROWCOUNT = 0) 
            BEGIN
                BREAK
            END
        IF OBJECT_ID('dbo.TestExceptions') IS NOT NULL 
            INSERT  dbo.TestExceptions
                    (data)
            VALUES  (@message_body)
	END
GO
--  Create a service broker queue to hold the events
CREATE QUEUE [ExceptionQueue]
WITH STATUS=ON,
ACTIVATION
(PROCEDURE_NAME = [ProcessException],
MAX_QUEUE_READERS = 1,
EXECUTE AS SELF)
GO
--  Create a service broker service receive the events
CREATE SERVICE [ExceptionService]
ON QUEUE [ExceptionQueue] ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification])
GO
CREATE ROUTE [ExceptionRoute]
WITH SERVICE_NAME = 'ExceptionService',
ADDRESS = 'LOCAL';
GO
CREATE EVENT NOTIFICATION [CaptureException]
ON SERVER
WITH FAN_IN
FOR EXCEPTION, EVENTLOG, ERRORLOG, USER_ERROR_MESSAGE
TO SERVICE 'ExceptionService', 'current database';
GO

Gokhan Varol

Moved by VidhyaSagar Monday, May 21, 2012 9:16 AM moving it to right category for proper response (From:SQL Server Database Engine)

All Replies

Tuesday, May 22, 2012 2:49 AM

Moderator

0

Hi Gokhan,

I try to reproduce this issue in my envrroment but failed. Could you please follow the sample in CREATE EVENT NOTIFICATION and check if it works in sql 2012 .
Best Regards,
Peja

Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
- Reply
- Quote
Tuesday, May 22, 2012 2:53 AM

0

I set the same sample from scratch couple more times and then dropped the database and started with a fresh database it worked eventually. I still do now why it did not work ( event did not get fired) and how to troubleshoot event not firing on event notification. It's little different then troubleshooting service broker since on event notification I am not the one starting the dialog.

If there is any good documentation on troubleshooting event notification that would be appreciated.

Thank you
Gokhan Varol
- Reply
- Quote

Wednesday, June 06, 2012 6:16 PM

Hi Gokhan,

I'm trying to do something very similar. I read that the cast to xml in the receive can be a problem. (It might not be xml?) I do the cast after the receive when I know the validation type is xml.

I also read that the end dialog and error messages are always possible and should be handled. I'm collecting batches of messages, so I check one conversation at a time to insure I close it only once. I included the procedure I'm using below. I have no idea if I'm doing this right. (I'm logging way to much for now because I'm experimenting.) Comments are welcome.

The selects below have helped me identify where problems are. Also, the error log might have errors if nothing shows up in the tables.

I just posted a Q re how to clean up the stuff in sys.conversation_endpoints. You might be interested in this also if you are trying to run this process long term.

Hope it helps.

-- see if the notification is stuck along the way to being processed
select * from sys.conversation_endpoints
select * from sys.transmission_queue
select * from monitor.EventNotificationQueue
-- check if the notification is still around
select * from sys.event_notifications
SELECT * FROM sys.server_event_notifications

ALTER PROCEDURE [monitor].[prReceiveEventNotificationQueue]
AS
BEGIN
  --
  SET NOCOUNT, XACT_ABORT ON;
  --
  DECLARE @error int, @rowcount int, @handle uniqueidentifier;
  --
  -- broker message types that must be handled
  --
  DECLARE @SbEndDialog nvarchar(256), @SbError nvarchar(256);
  SET @SbEndDialog = N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog';
  SET @SbError = N'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
  --
  -- Create a local table to receive the messages
  --
  DECLARE @Messages TABLE(
    [status] tinyint, -- 0=Ready 1=Received 2=Incomplete 3=Retained
    [priority] tinyint, 
    [queuing_order] bigint, 
    [conversation_group_id] uniqueidentifier, 
    [conversation_handle] uniqueidentifier, 
    [message_sequence_number] bigint, 
    [service_name] nvarchar(512),
    [service_id] int, 
    [service_contract_name] nvarchar(256), 
    [service_contract_id] int, 
    [message_type_name] nvarchar(256), 
    [message_type_id] int,
    [validation] nchar(2), -- E=Empty N=None X=XML
    [message_body] varbinary(MAX),
    [message_xml] xml,
    [post_time] datetime,
    [database_name] varchar(128),
    [event_type] varchar(60),
    [severity] smallint, 
    [error] int
  )
/*
USE [TemplateTest]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [monitor].[EventNotification](
	[EventNotificationId] [bigint] IDENTITY(1,1) NOT NULL,
    [status] tinyint, -- 0=Ready 1=Received 2=Incomplete 3=Retained
    [priority] tinyint, 
	[queuing_order] [bigint] NOT NULL,
	[conversation_group_id] [uniqueidentifier] NOT NULL,
    [conversation_handle] uniqueidentifier, 
	[message_sequence_number] [bigint] NOT NULL,
	[service_name] [nvarchar](512) NULL,
	[service_id] [int] NOT NULL,
	[service_contract_name] [nvarchar](256) NULL,
	[service_contract_id] [int] NOT NULL,
	[message_type_name] [nvarchar](256) NULL,
	[message_type_id] [int] NOT NULL,
	[validation] [nchar](2) NOT NULL,
    [message_body] varbinary(MAX) NULL,
    [message_xml] xml NULL,
	[post_time] [datetime] NULL,
	[database_name] [varchar](128) NULL,
	[event_type] [varchar](60) NULL,
	[severity] [smallint] NULL,
	[error] [int] NULL,
 CONSTRAINT [PK_monitor_EventNotification] PRIMARY KEY NONCLUSTERED 
(
	[EventNotificationId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
*/
  --
  -- Process notification queue in batches and loop until done.  A TRY-CATCH is used to log error 
  -- messages to DBA.monitor.ErrorLog to avoid raising error that would cause this procedure to fire 
  -- again.  
  --
  WHILE 1=1
  BEGIN 
    --
    -- if there is a problem, a rollback will leave items in the queue
    --
    BEGIN TRANSACTION; 
    --
    -- Get some of the messages and load them into the local table
    --
    WAITFOR(
      RECEIVE TOP (1000) 
        [status], -- 0=Ready 1=Received 2=Incomplete 3=Retained
        [priority], 
        [queuing_order], 
        [conversation_group_id], 
        [conversation_handle], 
        [message_sequence_number], 
        [service_name],
        [service_id], 
        [service_contract_name], 
        [service_contract_id], 
        [message_type_name], 
        [message_type_id],
        [validation],
        [message_body], 
        CAST(null as [xml]),
        null,
        null,
        null,
        null,
        null
      FROM TemplateTest.monitor.EventNotificationQueue
      INTO @Messages
    ), TIMEOUT 1000;
    --
    SELECT @rowcount = @@ROWCOUNT
    --
    IF @rowcount = 0 
    BEGIN
      ROLLBACK; 
      BREAK;
    END;
    
    UPDATE @Messages 
    SET message_xml = CASE 
        WHEN validation = 'X' THEN CAST(message_body AS XML)
        WHEN validation = 'E' THEN CAST('<empty />' AS XML)
        WHEN validation = 'N' THEN CAST('<binary><![CDATA[' 
          + CAST(message_body as varchar(MAX)) + ']]></binary>' AS XML)
        END
    
    UPDATE @Messages 
    SET post_time = message_xml.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime'), 
			--database_id = message_body.value('(/EVENT_INSTANCE/DatabaseID)[1]', 'smallint'), 
      database_name = message_xml.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(128)'), 
      event_type = message_xml.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(60)'), 
      severity = message_xml.value('(/EVENT_INSTANCE/Severity)[1]', 'smallint'), 
      error = message_xml.value('(/EVENT_INSTANCE/Error)[1]', 'int') 
    WHERE validation = 'X' and NOT message_body IS NULL
    
    SELECT TOP 1 @handle = [conversation_handle] 
    FROM @Messages 
    WHERE [message_type_name] IN (@SbEndDialog, @SbError) 
    ORDER BY [conversation_handle]
    SET @rowcount = @@ROWCOUNT
    
    WHILE (@rowcount > 0 AND NOT @handle IS NULL)
    BEGIN
      END CONVERSATION @handle
      SELECT TOP 1 @handle = [conversation_handle] 
      FROM @Messages 
      WHERE [message_type_name] IN (@SbEndDialog, @SbError) 
        AND [conversation_handle] > @handle 
      ORDER BY [conversation_handle]
      SET @rowcount = @@ROWCOUNT
    END
    
/* -- this seems to cause the notification to be dropped
    --declare @i int; set @i = 1
    SELECT TOP 1 @handle = [conversation_handle] FROM @Messages ORDER BY [conversation_handle]
    SET @rowcount = @@ROWCOUNT
    WHILE (@rowcount > 0 AND NOT @handle IS NULL)
    BEGIN
      --SELECT @i, @handle
      END CONVERSATION @handle
      SELECT TOP 1 @handle = [conversation_handle] FROM @Messages WHERE [conversation_handle] > @handle ORDER BY [conversation_handle]
      SET @rowcount = @@ROWCOUNT
      SET @i = @i + 1
    END
*/
  INSERT INTO [TemplateTest].[monitor].[EventNotification](
    [status]
    ,[priority]
    ,[queuing_order]
    ,[conversation_group_id]
    ,[conversation_handle]
    ,[message_sequence_number]
    ,[service_name]
    ,[service_id]
    ,[service_contract_name]
    ,[service_contract_id]
    ,[message_type_name]
    ,[message_type_id]
    ,[validation]
    ,[message_body]
    ,[message_xml]
    ,[post_time]
    ,[database_name]
    ,[event_type]
    ,[severity]
    ,[error]
    )
  SELECT 
    [status]
    ,[priority]
    ,[queuing_order]
    ,[conversation_group_id]
    ,[conversation_handle]
    ,[message_sequence_number]
    ,[service_name]
    ,[service_id]
    ,[service_contract_name]
    ,[service_contract_id]
    ,[message_type_name]
    ,[message_type_id]
    ,[validation]
    ,[message_body]
    ,[message_xml]
    ,[post_time]
    ,[database_name]
    ,[event_type]
    ,[severity]
    ,[error]
    FROM @Messages 
    --WHERE validation = 'X' AND error NOT IN (5701, 5703)
    WHERE error IS NULL OR error NOT IN (5701, 5703) -- looking for end dialog or error
    
--    SELECT * FROM @Messages 
    
--    SELECT * FROM @Messages 
--    WHERE validation = 'X' AND error NOT IN (5701, 5703)
    DELETE FROM @Messages; 
  
    COMMIT;
  END;
  --
  RETURN 0;
  --
END;

Randy in Marin

Can't seem to get Notification fired

All Replies

Products

Resources

Solutions

Updates

Trials

Related Sites

Training

Certifications

Other resources

Support by product

Other support links

Not an IT pro?