DDL Logon triggers-2005

Question

Post reply

DDL Logon triggers-2005

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

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

Questions:

1) Are you doing the ALTER TABLE commands on the same database? (LoggingDemo?)

2) Could you Drop and re-Create the Server Event Notification?

Thanks

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 2

Yep, I am executing alter table command under logging_demo database.

On your suggestion i executed below mentioned commands, still no luck.

DROP EVENT NOTIFICATION Notify_ALTER_T1

ON DATABASE

go

CREATE EVENT NOTIFICATION NotifyALTER_T1

ON DATABASE

FOR ALTER_TABLE

TO SERVICE 'NotifyService',

'8140a771-3c4b-4479-8ac0-81008ab17984';

GO

DROP EVENT NOTIFICATION Logging_Event_Notification

ON SERVER

go

CREATE EVENT NOTIFICATION Logging_Event_Notification

ON SERVER

FOR AUDIT_LOGIN, AUDIT_LOGOUT, AUDIT_LOGIN_FAILED

TO SERVICE 'LoggingService', 'Logging_Demo'

GO

alter table test1

add drgs int

Go

select * from loggingqueue

--Still no output from logginqueue table

Please help.

Thanks,

MJ

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

MANU (9/1/2008)
...
CREATE EVENT NOTIFICATION NotifyALTER_T1
ON DATABASE
FOR ALTER_TABLE
TO SERVICE 'NotifyService',
'8140a771-3c4b-4479-8ac0-81008ab17984';
...

Why are you using an explicit ID for your broker_instance? You should be using 'current database' here. Plus the broker_instance for a database changes everytime your restart it. Execute this command to check it:

Select * From Master.sys.databases

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

Oh, I see, you are using the GUID printed in the example in BOL. Well, that won't work. You need to execute the aforementioned command and check the service_broker_guid column of the database that you want to receive the event. Or just user 'current database' for the current database.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 5

Okie, I dropped that notification and created new one as per your suggestion:

Create EVENT NOTIFICATION NotifyALTER_T1

ON DATABASE

FOR ALTER_TABLE

TO SERVICE 'NotifyService',

'Logging_Demo';

Still no luck after running:

alter table test1

add drgs1 int

Go

select * from loggingqueue

--Still no output from logginqueue table

Help needed..

MJ

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

No, it's like this:

Create EVENT NOTIFICATION NotifyALTER_T1

ON DATABASE

FOR ALTER_TABLE

TO SERVICE 'NotifyService',

'current database';

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 7

I dropped the old one and executed the below mentioned statements in sequence on logging_demo database:

Create EVENT NOTIFICATION NotifyALTER_T1

ON DATABASE

FOR ALTER_TABLE

TO SERVICE 'NotifyService','current database';

alter table test1

add drgs112 int

Go

select * from loggingqueue

--no output 0 rows

No luck. Please help.

MJ

RBarryYoung SSC Guru Points: 143329 More actions · Answer 8

OK, I will try to step forward through it then.

Execute these commands and post their output here, please:

Select * from sys.databases Where name = 'Logging_Demo'

Select * from sys.transmission_queue

select * from sys.event_notifications

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 9

Thanks for your time and efforts.

Please find attached output of the three statments.

MJ

RBarryYoung SSC Guru Points: 143329 More actions · Answer 10

Huh. Well, I cannot see anything wrong with it now. Sorry, to say Manu, but I am out of ideas.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 11

Actually, I did think of one other thing, try this command:

Select * from dbo.NotifyQueue

And let us know what it shows.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 12

Please find attached output of : Select * from dbo.NotifyQueue

Thanks for all your help.

MJ

RBarryYoung SSC Guru Points: 143329 More actions · Answer 13

OK, actually it looks like everything is working now. The ALTER TABLE Event messages are in the queue.

What is reading the queue and what makes you say that it is "not working?" I notice that there is no Activated procedure associated with the queue, so you must be using some other way to Receive and process the messages?

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 14

Thanks a lot for all your help and brilliant patience. Impressed me a lot.

Actually my problem got resolved the moment I used as per your suggestion:

Create EVENT NOTIFICATION NotifyALTER_T1

ON DATABASE

FOR ALTER_TABLE

TO SERVICE 'NotifyService',

'current database';

Due to two queues notifyqueue and loggin queue I got confused and was checking wrong queue(loggingQueue) correct one was notifyqueue and it is capturing alter table events too(as you pointed just now).

Now everything is fine. I will follow the doc mentioned by me earlier and will proceed.

Thanks once again,

MJ

RBarryYoung SSC Guru Points: 143329 More actions · Answer 15

*whew!* Glad I could help. 🙂

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]