Getting Started with SQL Server Event Notifications

  • Jonathan Kehayias

    One Orange Chip

    Points: 26672

    vinay.kumar (7/6/2012)


    HI,

    Thanks For the Post.

    I tried the sample Event Notification given in the blog. And it looks all fine.

    For the each Event(ERRORLOG) it is using one Conversation Group for all the raised errors. SO i manually ended the conversation and then i raised a custom exception. This time no messaes sent to any Queue.

    Please let me know your thought

    Thanks,

    Vinay K

    Why would you do that? The conversation being reused by the Event Notification is normal behavior, and reuse of conversations can be a a major performance improvement.

    http://rusanu.com/2007/04/25/reusing-conversations/%5B/url%5D

    Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
    My Blog | Twitter | MVP Profile
    Training | Consulting | Become a SQLskills Insider
    Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]

  • John Esraelo-498130

    SSCertifiable

    Points: 5894

    Hi Jonathan,

    Before I ask a question I wanted to say that I really like your articles, especially in the events area.

    Here is the question:

    I have create a queue for auditing database management on alter, create, drop, etc.

    everything looks fine, I can query the queue and displaying the records..

    But, for some reason this queue becomes "disabled" within a minute after re-enabling and I have to manually or programmatically re-enable it.

    I have created couple of other ones that work fine, such as audit login failed, and one adapted from your awesome code on AutoGrowFile..

    Any advise would be grand

    thx again Jonathan

    Cheers,John Esraelo

  • Jonathan Kehayias

    One Orange Chip

    Points: 26672

    John Esraelo-498130 (11/10/2012)


    Hi Jonathan,

    Before I ask a question I wanted to say that I really like your articles, especially in the events area.

    Here is the question:

    I have create a queue for auditing database management on alter, create, drop, etc.

    everything looks fine, I can query the queue and displaying the records..

    But, for some reason this queue becomes "disabled" within a minute after re-enabling and I have to manually or programmatically re-enable it.

    I have created couple of other ones that work fine, such as audit login failed, and one adapted from your awesome code on AutoGrowFile..

    Any advise would be grand

    thx again Jonathan

    Hey John,

    My first guess would be that you have a poison message in the queue that is causing the activation procedure to rollback and after 5 rollbacks it will automatically disable the queue. To troubleshoot this, I would disable activation and then enable the queue and look at the next message on it to see what is wrong with it. I'd also see why the activation procedure has to rollback during processing of the message. To do this:

    -- Turn activation off

    ALTER QUEUE [YourQueue]

    WITH ACTIVATION (

    STATUS = OFF,

    EXECUTE AS OWNER);

    -- Enable the queue

    ALTER QUEUE [YourQueue]

    WITH STATUS = ON;

    Then just do a standard SELECT against the queue to view it's contents.

    SELECT *

    FROM [YourQueue];

    You can also clear the items in the queue by using RECEIVE:

    DECLARE @message_body XML;

    -- Get the top message from the queue

    RECEIVE TOP (1)

    @message_body = CAST([message_body] AS XML)

    FROM [YourQueue]);

    Hopefully that helps you resolve the problems.

    Cheers,

    Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
    My Blog | Twitter | MVP Profile
    Training | Consulting | Become a SQLskills Insider
    Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]

  • John Esraelo-498130

    SSCertifiable

    Points: 5894

    Jonathan Kehayias (11/10/2012)


    John Esraelo-498130 (11/10/2012)


    Hi Jonathan,

    Before I ask a question I wanted to say that I really like your articles, especially in the events area.

    Here is the question:

    I have create a queue for auditing database management on alter, create, drop, etc.

    everything looks fine, I can query the queue and displaying the records..

    But, for some reason this queue becomes "disabled" within a minute after re-enabling and I have to manually or programmatically re-enable it.

    I have created couple of other ones that work fine, such as audit login failed, and one adapted from your awesome code on AutoGrowFile..

    Any advise would be grand

    thx again Jonathan

    Hey John,

    My first guess would be that you have a poison message in the queue that is causing the activation procedure to rollback and after 5 rollbacks it will automatically disable the queue. To troubleshoot this, I would disable activation and then enable the queue and look at the next message on it to see what is wrong with it. I'd also see why the activation procedure has to rollback during processing of the message. To do this:

    -- Turn activation off

    ALTER QUEUE [YourQueue]

    WITH ACTIVATION (

    STATUS = OFF,

    EXECUTE AS OWNER);

    -- Enable the queue

    ALTER QUEUE [YourQueue]

    WITH STATUS = ON;

    Then just do a standard SELECT against the queue to view it's contents.

    SELECT *

    FROM [YourQueue];

    You can also clear the items in the queue by using RECEIVE:

    DECLARE @message_body XML;

    -- Get the top message from the queue

    RECEIVE TOP (1)

    @message_body = CAST([message_body] AS XML)

    FROM [YourQueue]);

    Hopefully that helps you resolve the problems.

    Cheers,

    Thank you Jonathan, I will give it a shot in a minute and keep you posted of the results .

    thx again

    Cheers,John Esraelo

  • John Esraelo-498130

    SSCertifiable

    Points: 5894

    FYI

    I wonder if my AUDIT_DATABASE_MANAGEMENT_EVENT statement in the paragraph in below does not exist or typed incorrectly..

    so, I am going to search on internet / msdn and see what I can find out ..

    CREATE EVENT NOTIFICATION [CaptureAuditDatabaseManagementEvents]

    ON SERVER

    WITH FAN_IN

    FOR AUDIT_DATABASE_MANAGEMENT_EVENT

    TO SERVICE 'AuditDatabaseManagementEventService', 'current database';

    GO

    Cheers,John Esraelo

  • John Esraelo-498130

    SSCertifiable

    Points: 5894

    so much for my findings 😉 this tells me that my audit argument in the paragraph may be correct.

    http://msdn.microsoft.com/en-us/library/ms182454%28v=sql.90%29.aspx

    alright, the search of other possible areas continues..

    Cheers,John Esraelo

  • Jonathan Kehayias

    One Orange Chip

    Points: 26672

    Send me the scripts for the Queue, Service, Event Notification, and Activation Procedure by email through my blog. Let me know what specific version of SQL Server you are running, and the Edition, or just copy/paste the output of SELECT @@VERSION into the message so that I can match your environment and I'll see if I can repro this and offer a complete fix.

    Did you dump all of the messages from the queue and look at them to see if there is a problem with one?

    Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
    My Blog | Twitter | MVP Profile
    Training | Consulting | Become a SQLskills Insider
    Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]

  • John Esraelo-498130

    SSCertifiable

    Points: 5894

    I used your TEST DB creation method, generated 3 statements in the queue.. viewed the content of the queue table did not look any strange or anything like that.. also the queue captured some statements from another DB's transactional backup..

    then I placed couple of email notifications as an echo print for debugging in the USP..

    and I was just checking the email to see how far in the USP the process go in..

    create PROCEDURE [dbo].[USP_ProcessAuditDatabaseManagementEvents]

    WITH EXECUTE AS OWNER

    AS

    DECLARE @message_body XML;

    DECLARE @message_sequence_number INT;

    DECLARE @dialog UNIQUEIDENTIFIER;

    DECLARE @email_message NVARCHAR(MAX);

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'LocalServer', -- your defined email profile

    @recipients = 'JohnE.SQL@gmail.com', -- your email

    @subject = 'Audit Database Management Event Notification',

    @body = 'echo print to the message_body in USP';

    WHILE ( 1 = 1 )

    BEGIN

    BEGIN TRANSACTION;

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'LocalServer', -- your defined email profile

    @recipients = 'JohnE.SQL@gmail.com', -- your email

    @subject = 'Audit Database Management Event Notification',

    @body = 'after reading a records ';

    -- 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)

    FROM dbo.AuditDatabaseManagementEventQueue

    ), TIMEOUT 1000; -- if queue empty for 1 sec, give UPDATE AND GO away

    sure, no problem, I will send you the code..

    it is basically like the other ones that were adapted from yours and they work fabulously ..

    that's why my suspicion was on audit_database_management...etc.. class / event name..

    Cheers,John Esraelo

  • John Esraelo-498130

    SSCertifiable

    Points: 5894

    Jonathan,

    Wasn't able to find your email link on the blog.. but here is the code..

    as you notice that I have commented out most majority of the elements to be emailed ..

    I have a suspicion, as I mentioned before, on the AUDIT_DATABASE_MANAGEMENT_EVENT

    and here is the version

    Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)

    Jul 9 2008 14:43:34

    Copyright (c) 1988-2008 Microsoft Corporation

    Enterprise Edition on Windows NT 5.2 <X86> (Build 3790: Service Pack 2)

    -- Using msdb prevents the need for certificate signing the

    -- activation procedure to execute sp_send_dbmail across

    -- databases

    USE [msdb];

    GO

    -- Drop the notification if it exists

    IF EXISTS ( SELECT *

    FROM sys.server_event_notifications

    WHERE name = N'CaptureAuditDatabaseManagementEvents' )

    BEGIN

    DROP EVENT NOTIFICATION CaptureAuditDatabaseManagementEvents ON SERVER;

    END

    -- Drop the route if it exists

    IF EXISTS ( SELECT *

    FROM sys.routes

    WHERE name = N'AuditDatabaseManagementEventRoute' )

    BEGIN

    DROP ROUTE AuditDatabaseManagementEventRoute;

    END

    -- Drop the service if it exists

    IF EXISTS ( SELECT *

    FROM sys.services

    WHERE name = N'AuditDatabaseManagementEventService' )

    BEGIN

    DROP SERVICE AuditDatabaseManagementEventService;

    END

    -- Drop the queue if it exists

    IF EXISTS ( SELECT *

    FROM sys.service_queues

    WHERE name = N'AuditDatabaseManagementEventQueue' )

    BEGIN

    DROP QUEUE AuditDatabaseManagementEventQueue;

    END

    -- Create a service broker queue to hold the events

    CREATE QUEUE [AuditDatabaseManagementEventQueue]

    WITH STATUS=ON;

    GO

    -- Create a service broker service receive the events

    CREATE SERVICE [AuditDatabaseManagementEventService]

    ON QUEUE [AuditDatabaseManagementEventQueue] ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);

    GO

    -- Create a service broker route to the service

    CREATE ROUTE [AuditDatabaseManagementEventRoute]

    WITH SERVICE_NAME = 'AuditDatabaseManagementEventService',

    ADDRESS = 'LOCAL';

    GO

    -- Create the event notification to capture the events

    CREATE EVENT NOTIFICATION [CaptureAuditDatabaseManagementEvents]

    ON SERVER

    WITH FAN_IN

    FOR AUDIT_DATABASE_MANAGEMENT_EVENT

    TO SERVICE 'AuditDatabaseManagementEventService', 'current database';

    GO

    -- ==========================================================================================

    USE [msdb];

    GO

    SELECT

    ApplicationName = message_body.value('(/EVENT_INSTANCE/ApplicationName )[1]','nvarchar(32)')

    , DatabaseName = message_body.value('(/EVENT_INSTANCE/DatabaseName )[1]','nvarchar(32)')

    , DBUserName = message_body.value('(/EVENT_INSTANCE/DBUserName )[1]','nvarchar(32)')

    , EventSubClass = message_body.value('(/EVENT_INSTANCE/EventSubClass )[1]','int' )

    , (case message_body.value('(/EVENT_INSTANCE/EventSubClass )[1]','int' ) when 1 then 'Create' when 2 then 'Alter' when 3 then 'Drop' when 4 then 'Dump' when 11 then 'Load' else 'Undefined' end) SubClassNameDesc

    , HostName = message_body.value('(/EVENT_INSTANCE/HostName )[1]','nvarchar(32)')

    , LoginName = message_body.value('(/EVENT_INSTANCE/LoginName )[1]','nvarchar(32)')

    , NTDomainName = message_body.value('(/EVENT_INSTANCE/NTDomainName )[1]','nvarchar(32)')

    , NTUserName = message_body.value('(/EVENT_INSTANCE/NTUserName )[1]','nvarchar(32)')

    , ObjectName = message_body.value('(/EVENT_INSTANCE/ObjectName )[1]','nvarchar(32)')

    , OwnerName = message_body.value('(/EVENT_INSTANCE/OwnerName )[1]','nvarchar(32)')

    , ServerName = message_body.value('(/EVENT_INSTANCE/ServerName )[1]','nvarchar(32)')

    , SessionLoginName = message_body.value('(/EVENT_INSTANCE/SessionLoginName)[1]','nvarchar(32)')

    , StartTime = message_body.value('(/EVENT_INSTANCE/StartTime )[1]','datetime2' )

    , Success = message_body.value('(/EVENT_INSTANCE/Success )[1]','int' )

    , TextData = message_body.value('(/EVENT_INSTANCE/TextData )[1]','nvarchar(32)')

    FROM ( SELECT CAST(message_body AS XML) AS message_body

    FROM [AuditDatabaseManagementEventQueue]

    ) AS Tab;

    -- ==========================================================================================

    -- Create the Activation Stored Procedure to Process the Queue

    IF EXISTS ( SELECT *

    FROM dbo.sysobjects

    WHERE id = OBJECT_ID(N'[dbo].[USP_ProcessAuditDatabaseManagementEvents]')

    AND OBJECTPROPERTY(id, N'IsProcedure') = 1 )

    DROP PROCEDURE [dbo].[USP_ProcessAuditDatabaseManagementEvents];

    GO

    create PROCEDURE [dbo].[USP_ProcessAuditDatabaseManagementEvents]

    WITH EXECUTE AS OWNER

    AS

    DECLARE @message_body XML;

    DECLARE @message_sequence_number INT;

    DECLARE @dialog UNIQUEIDENTIFIER;

    DECLARE @email_message NVARCHAR(MAX);

    WHILE ( 1 = 1 )

    BEGIN

    BEGIN TRANSACTION;

    -- 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)

    FROM dbo.AuditDatabaseManagementEventQueue

    ), TIMEOUT 1000; -- if queue empty for 1 sec, give UPDATE AND GO away

    -- If we didn't get anything, bail out

    IF ( @@ROWCOUNT = 0 )

    BEGIN

    ROLLBACK TRANSACTION;

    BREAK;

    END

    declare

    @ApplicationName nvarchar(32) -- nvarchar

    , @DatabaseName nvarchar(32) -- nvarchar

    , @DBUserName nvarchar(32) -- nvarchar

    , @EventSubClass int -- int

    , @HostName nvarchar(32) -- nvarchar

    , @LoginName nvarchar(32) -- nvarchar

    , @NTDomainName nvarchar(32) -- nvarchar

    , @NTUserName nvarchar(32) -- nvarchar

    , @ObjectName nvarchar(32) -- nvarchar

    , @OwnerName nvarchar(32) -- nvarchar

    , @ServerName nvarchar(32) -- nvarchar

    , @SessionLoginName nvarchar(32) -- nvarchar

    , @StartTime nvarchar(32) -- datetime

    , @Success int -- int

    , @TextData nvarchar(32) -- ntext

    select

    @ApplicationName = message_body.value('(/EVENT_INSTANCE/ApplicationName )[1]','nvarchar(32)')

    , @DatabaseName = message_body.value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(32)')

    , @DBUserName = message_body.value('(/EVENT_INSTANCE/DBUserName)[1]','nvarchar(32)')

    , @EventSubClass = message_body.value('(/EVENT_INSTANCE/EventSubClass)[1]','int' )

    , @HostName = message_body.value('(/EVENT_INSTANCE/HostName)[1]','nvarchar(32)')

    , @LoginName = message_body.value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(32)')

    , @NTDomainName = message_body.value('(/EVENT_INSTANCE/NTDomainName)[1]','nvarchar(32)')

    , @NTUserName = message_body.value('(/EVENT_INSTANCE/NTUserName)[1]','nvarchar(32)')

    , @ObjectName = message_body.value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(32)')

    , @OwnerName = message_body.value('(/EVENT_INSTANCE/OwnerName)[1]','nvarchar(32)')

    , @ServerName = message_body.value('(/EVENT_INSTANCE/ServerName)[1]','nvarchar(32)')

    , @SessionLoginName = message_body.value('(/EVENT_INSTANCE/SessionLoginName)[1]','nvarchar(32)')

    , @StartTime = message_body.value('(/EVENT_INSTANCE/StartTime)[1]','nvarchar(32)')

    , @Success = message_body.value('(/EVENT_INSTANCE/Success)[1]','int' )

    , @TextData = message_body.value('(/EVENT_INSTANCE/TextData)[1]','nvarchar(32)')

    -- Generate formatted email message

    select

    @email_message = 'Audit DB Mgmt Event Occured::'

    --+ char(10) + 'ApplicationName ' + @ApplicationName

    --+ char(10) + 'DatabaseName ' + @DatabaseName

    --+ char(10) + 'DBUserName ' + @DBUserName

    --+ char(10) + 'EventSubClass ' + (case @EventSubClass when 1 then 'Create' when 2 then 'Alter' when 3 then 'Drop' when 4 then 'Dump' when 11 then 'Load' else 'Undefined' end)

    --+ char(10) + 'HostName ' + @HostName

    --+ char(10) + 'LoginName ' + @LoginName

    --+ char(10) + 'NTDomainName ' + @NTDomainName

    --+ char(10) + 'NTUserName ' + @NTUserName

    --+ char(10) + 'ObjectName ' + @ObjectName

    --+ char(10) + 'OwnerName ' + @OwnerName

    --+ char(10) + 'ServerName ' + @ServerName

    --+ char(10) + 'SessionLoginName ' + @SessionLoginName

    --+ char(10) + 'StartTime ' + @StartTime

    --+ char(10) + 'Success ' + @Success

    --+ char(10) + 'TextData ' + @TextData

    -- Send email using Database Mail

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'LocalServer', -- your defined email profile

    @recipients = 'JohnE.SQL@gmail.com', -- your email

    @subject = 'Audit Database Management Event Notification',

    @body = @email_message;

    -- Commit the transaction. At any point before this, we could roll

    -- back. The received message would be back on the queue AND the

    -- response wouldn't be sent.

    COMMIT TRANSACTION;

    END

    GO

    -- Alter the queue to use the activation procedure

    ALTER QUEUE [AuditDatabaseManagementEventQueue]

    WITH STATUS=ON,

    ACTIVATION

    (STATUS=ON,

    PROCEDURE_NAME = [dbo].[USP_ProcessAuditDatabaseManagementEvents],

    MAX_QUEUE_READERS = 1,

    EXECUTE AS OWNER);

    GO

    Cheers,John Esraelo

  • Jonathan Kehayias

    One Orange Chip

    Points: 26672

    Your activation stored procedure is the problem. Before you configured the queue for activation did you test the procedure by executing it? I am going to bet not in this case. If you run the procedure as written it will provide the following error:

    Msg 4121, Level 16, State 1, Procedure USP_ProcessAuditDatabaseManagementEvents, Line 47

    Cannot find either column "message_body" or the user-defined function or aggregate "message_body.value", or the name is ambiguous.

    Msg 266, Level 16, State 2, Procedure USP_ProcessAuditDatabaseManagementEvents, Line 47

    Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.

    The problem is the code below:

    select

    @ApplicationName = message_body.value('(/EVENT_INSTANCE/ApplicationName )[1]','nvarchar(32)')

    , @DatabaseName = message_body.value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(32)')

    , @DBUserName = message_body.value('(/EVENT_INSTANCE/DBUserName)[1]','nvarchar(32)')

    , @EventSubClass = message_body.value('(/EVENT_INSTANCE/EventSubClass)[1]','int' )

    , @HostName = message_body.value('(/EVENT_INSTANCE/HostName)[1]','nvarchar(32)')

    , @LoginName = message_body.value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(32)')

    , @NTDomainName = message_body.value('(/EVENT_INSTANCE/NTDomainName)[1]','nvarchar(32)')

    , @NTUserName = message_body.value('(/EVENT_INSTANCE/NTUserName)[1]','nvarchar(32)')

    , @ObjectName = message_body.value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(32)')

    , @OwnerName = message_body.value('(/EVENT_INSTANCE/OwnerName)[1]','nvarchar(32)')

    , @ServerName = message_body.value('(/EVENT_INSTANCE/ServerName)[1]','nvarchar(32)')

    , @SessionLoginName = message_body.value('(/EVENT_INSTANCE/SessionLoginName)[1]','nvarchar(32)')

    , @StartTime = message_body.value('(/EVENT_INSTANCE/StartTime)[1]','nvarchar(32)')

    , @Success = message_body.value('(/EVENT_INSTANCE/Success)[1]','int' )

    , @TextData = message_body.value('(/EVENT_INSTANCE/TextData)[1]','nvarchar(32)')

    It should be the code below:

    select

    @ApplicationName = @message_body.value('(/EVENT_INSTANCE/ApplicationName )[1]','nvarchar(32)')

    , @DatabaseName = @message_body.value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(32)')

    , @DBUserName = @message_body.value('(/EVENT_INSTANCE/DBUserName)[1]','nvarchar(32)')

    , @EventSubClass = @message_body.value('(/EVENT_INSTANCE/EventSubClass)[1]','int' )

    , @HostName = @message_body.value('(/EVENT_INSTANCE/HostName)[1]','nvarchar(32)')

    , @LoginName = @message_body.value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(32)')

    , @NTDomainName = @message_body.value('(/EVENT_INSTANCE/NTDomainName)[1]','nvarchar(32)')

    , @NTUserName = @message_body.value('(/EVENT_INSTANCE/NTUserName)[1]','nvarchar(32)')

    , @ObjectName = @message_body.value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(32)')

    , @OwnerName = @message_body.value('(/EVENT_INSTANCE/OwnerName)[1]','nvarchar(32)')

    , @ServerName = @message_body.value('(/EVENT_INSTANCE/ServerName)[1]','nvarchar(32)')

    , @SessionLoginName = @message_body.value('(/EVENT_INSTANCE/SessionLoginName)[1]','nvarchar(32)')

    , @StartTime = @message_body.value('(/EVENT_INSTANCE/StartTime)[1]','nvarchar(32)')

    , @Success = @message_body.value('(/EVENT_INSTANCE/Success)[1]','int' )

    , @TextData = @message_body.value('(/EVENT_INSTANCE/TextData)[1]','nvarchar(32)')

    You referenced a column name and not the variable that you set the message_body to so it errors out and you get the rollback, so 5 times and your queue disables. Anytime you are building an activation procedure, you need to run it to test that it works without error before configuring the queue for activation using the procedure or you can have this problem.

    Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
    My Blog | Twitter | MVP Profile
    Training | Consulting | Become a SQLskills Insider
    Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]

  • Jonathan Kehayias

    One Orange Chip

    Points: 26672

    BTW:

    You also need to CAST the @Success variable to VARCHAR in you concatenation code for the email body:

    + char(10) + 'Success ' + CAST(@Success AS VARCHAR)

    Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
    My Blog | Twitter | MVP Profile
    Training | Consulting | Become a SQLskills Insider
    Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]

  • John Esraelo-498130

    SSCertifiable

    Points: 5894

    Jonathan,

    OMG, how embarrassing this is for me.. hehe 😉

    it is working great..

    thx again

    Cheers,John Esraelo

  • Jonathan Kehayias

    One Orange Chip

    Points: 26672

    Hey John,

    It's an easy mistake to make, but I bet it will never happen again. Ask me how I know this, I made a very similar mistake 4-5 years ago when I first worked with Event Notifications. This is why I suggested removing the activation from the queue and testing whether it would go back to disabled. If it doesn't the problem is within the activation procedure, so executing it separately will tell you where the problem is. Always test the procedure before binding it to the queue so that you know if it will be a problem or not.

    Have a good weekend.

    Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
    My Blog | Twitter | MVP Profile
    Training | Consulting | Become a SQLskills Insider
    Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]

  • John Esraelo-498130

    SSCertifiable

    Points: 5894

    Jonathan,

    You are right, will not occur again. I can see how that happened to me. First of all I should have run the USP and check.. secondly, I had copied and pasted from the select statement in the script that the message_body was an Alias and not the actual object 😉

    Thank you again and 2 heads always work better than mine, I mean better than 1 . 😉

    Cheers,John Esraelo

Viewing 14 posts - 16 through 29 (of 29 total)

You must be logged in to reply to this topic. Login to reply