June 28, 2010 at 7:14 pm
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
June 29, 2010 at 5:24 pm
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';
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply