Service Broker/Event Notification Problem; Event Notification stopping unexpectedly

  • I am running SQL 2005 Enterprise Edition; I am SA on this server. I am using EVENT NOTIFICATION for DDL_DATABASE_LEVEL_EVENTS to track DDL changes. If I do "USE TRACKED_DB; DROP TABLE TRACKED_DB.DBO.SOMETABLE" it works without problems. But if I do "USE OTHER_DB; DROP TABLE TRACKED_DB.DBO.SOMETABLE" it appears to do a DROP EVENT NOTIFICATION-- at any rate, I have to re-do my CREATE EVENT NOTIFICATION to continue tracking DDL changes.

    Is this expected behavior? (err... It wasn't for me!) ...Am I doing something grossly wrong? Thanks for any help.

    You need 2 dbs to test this; TestAlerts and Admin. There are no other special requirements needed for them.

    --------------------------------------------------------------------------

    --PART 1

    -- this db will hold a permanent table for the events tracked

    -- In my environ I may do the PART 2 CODE for several dbs on a server

    -- This table will periodically RECEIVE rows from the queue-- from all tracked dbs on the server

    -- ...And eventually send THOSE to a central server fpr reporting

    -- I planned to do the RECEIVE and the send to central via periodic SQL JOBs.

    --------------------------------------------------------------------------

    USE ADMIN

    CREATE TABLE ReceivedDdlDbEvents(

    rowid bigint identity,

    servername varchar(200),

    dbname varchar(200),

    service_instance_id UNIQUEIDENTIFIER,

    handle UNIQUEIDENTIFIER,

    message_sequence_number BIGINT,

    service_name NVARCHAR(512),

    service_contract_name NVARCHAR(256),

    message_type_name NVARCHAR(256),

    validation NCHAR,

    message_body XML) ;

    --------------------------------------------------------------------------

    --PART 2

    -- Set it up

    --------------------------------------------------------------------------

    ALTER DATABASE TestAlerts SET ENABLE_BROKER

    USE TestAlerts

    go

    CREATE QUEUE queueDdlDbEvents WITH STATUS = ON--SBQ/SERVICE BROKER QUEUE: holds the events

    go

    CREATE SERVICE svcDdlDbEvents ON QUEUE queueDdlDbEvents ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification])--SBS/SERVICE BROKER SERVICE: receives the incoming messages (events) and stores them in the SBQ

    go

    CREATE EVENT NOTIFICATION notify_DdlDbEvents ON DATABASE WITH fan_in FOR DDL_DATABASE_LEVEL_EVENTS TO SERVICE 'svcDdlDbEvents', 'current database';--SBN/SERVICE BROKER NOTIFICATION: writes to the SBS (which then takes rcvd messages/evts and sends to SBQ

    go

    --------------------------------------------------------------------------

    --PART 3

    -- Here are a series of DDL commands;

    -- Run a group of lines from PART 3 and check by running PART 4 after

    --------------------------------------------------------------------------

    USE TestAlerts

    CREATE TABLE FOO1(bar char(1))

    CREATE TABLE FOO2(bar char(1))

    CREATE TABLE FOO3(bar char(1))

    CREATE TABLE FOO4(bar char(1))

    CREATE TABLE FOO5(bar char(1))

    CREATE TABLE FOO6(bar char(1))

    -- if you run part 4 after the 7 lines above you should see 6 rows in

    --SELECT * FROM ADMIN.dbo.ReceivedDdlDbEvents

    USE TestAlerts

    DROP TABLE FOO1

    DROP TABLE DBO.FOO2

    DROP TABLE TestAlerts.DBO.FOO3

    -- if you run part 4 after the 4 lines above you should see 4 MORE (10 total) rows in

    --SELECT * FROM ADMIN.dbo.ReceivedDdlDbEvents

    USE ADMIN

    DROP TABLE TestAlerts.DBO.FOO4-- uhoh; didn't get into queue

    go

    SELECT * FROM TESTALERTS.dbo.queueDdlDbEvents -- do this line or run part 4; same result

    USE TestAlerts

    DROP TABLE TestAlerts.DBO.FOO5-- uhoh; neither did this

    go

    SELECT * FROM TESTALERTS.dbo.queueDdlDbEvents -- do this line or run part 4; same result

    -- what the...? WHY do I need to re-CREATE EVENT??

    USE TestAlerts

    CREATE EVENT NOTIFICATION notify_DdlDbEvents ON DATABASE WITH fan_in FOR DDL_DATABASE_LEVEL_EVENTS TO SERVICE 'svcDdlDbEvents', 'current database';--SBN/SERVICE BROKER NOTIFICATION: writes to the SBS (which then takes rcvd messages/evts and sends to SBQ

    go

    DROP TABLE TestAlerts.DBO.FOO6-- and now it works again:

    SELECT * FROM TESTALERTS.dbo.queueDdlDbEvents -- do this line or run part 4; same result

    --------------------------------------------------------------------------

    --PART 4

    -- This should get queued data into the ADMIN table

    --------------------------------------------------------------------------

    USE TestAlerts

    DECLARE @ReceivedDdlDbEvents TABLE(

    service_instance_id UNIQUEIDENTIFIER,

    handle UNIQUEIDENTIFIER,

    message_sequence_number BIGINT,

    service_name NVARCHAR(512),

    service_contract_name NVARCHAR(256),

    message_type_name NVARCHAR(256),

    validation NCHAR,

    message_body XML) ;

    -- RECEIVE it into @TV ----------------------------------------

    RECEIVE

    conversation_group_id,

    conversation_handle,

    message_sequence_number,

    service_name,

    service_contract_name,

    message_type_name,

    validation,

    message_body

    FROM TESTALERTS.dbo.queueDdlDbEvents

    INTO @ReceivedDdlDbEvents

    -- THEN save it to a real table -------------------------------

    INSERT INTO ADMIN.DBO.ReceivedDdlDbEvents SELECT @@servername,db_name(),* FROM @ReceivedDdlDbEvents

    -- see?

    SELECT * FROM ADMIN.dbo.ReceivedDdlDbEvents

    SELECT * FROM TESTALERTS.dbo.queueDdlDbEvents


    Cursors are useful if you don't know SQL

  • A little more fiddling and I've got the (one-line) fix:

    Changed

    CREATE EVENT NOTIFICATION notify_DdlDbEvents ON [highlight]DATABASE[/highlight] WITH fan_in FOR DDL_DATABASE_LEVEL_EVENTS TO SERVICE 'svcDdlDbEvents', 'current database';

    To

    CREATE EVENT NOTIFICATION notify_DdlDbEvents ON [highlight]SERVER[/highlight] WITH fan_in FOR DDL_DATABASE_LEVEL_EVENTS TO SERVICE 'svcDdlDbEvents', 'current database';


    Cursors are useful if you don't know SQL

Viewing 2 posts - 1 through 1 (of 1 total)

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