DDL Logon triggers-2005

Question

DDL Logon triggers-2005

MANU-J.

SSC-Dedicated

Points: 31126
More actions
August 30, 2008 at 7:24 pm

#131005

Hi,
I was going thru logontriggers article on http://www.sqlservercentral.com/articles/SQLServerCentral.com/sqlserver2005logontriggers/2366/
I tried to implement it on my sql2005 machine but even after creating queue and service etc. messages are not getting queued up as per this article.
I enabled database for service broker.
Please help me where i need to do changes to get messages in queue.
MJ

Viewing 15 posts - 1 through 15 (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

Try to fire the Logon trigger, then do a: Select * From sys.transmission_queue and post the results here.

[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]

Fraggle-805517 SSCrazy Eights Points: 8907 More actions · Answer 2

You may also want to check that when you created the queue that the max_queue_reader is set to something greater than 0. I cannot remember what it defaults to, but if your messages are getting to the queue, and just not getting pulled out, that might be the issue. I know that it was for me once.

Hope that helps.

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

I am getting the below mentioned message in under transmission_status column in Select * from sys.transmission_queue output:

Could not find the target broker in the local SQL Server instance.

Please help me in troubleshooting it.

MJ

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

Please post the entire output from sys.transmission_queue.

[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

Attached is the output of Select * from sys.transmission_queue.

Thanks,

MJ

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

These are NOT Logon Event Notifications. These are ALTER TABLE 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 7

Forgot to tell you sorry, I changed the logon event to alter table as I was not able to capture server logon events.

Still when I change the schema of any table it doesn't appear under queue.

Thanks for your replies and time.

MJ

RBarryYoung SSC Guru Points: 143329 More actions · Answer 8

OK, you should try to reset the Service Broker on this DB then. Execute this commands and try you trigger again:

USE Master

ALTER Database [Your DB] Set Enable_Broker with rollback immediate

-- makes sure that the Service Broker is enabled in this database

--("Rolback Immediate" kicks everyone else out and rollsback anything pending

-- it is for integrity)

USE [Your DB]

create master key encryption by password = 'Password1'

-- sometimes Service Broker, will not work without this.

[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

I executed your sentences and its still not tracking any Alter table events.

Please help. No rows in Select * from 'LoggingQueue' output.

MJ

RBarryYoung SSC Guru Points: 143329 More actions · Answer 10

In SSMS, go to the databases's Service Broker section and script out all of the non-system items: Message Types, Contracts, Services, Queues. Routes and Remote Service Bindings. Sorry, but you will need to do them one at a time as the Script Generator will not do them.

Then post them all here, as is. It is very important that the text is exactly as listed and not retyped.

[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]

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 11

did you also have a look at this article ?

http://sqljunkies.com/WebLog/ktegels/archive/2006/11/09/25306.aspx

You can off course always make a comment to the article, so Frederik can reply himself.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

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

Hi,

Please find all attached.

Thanks again for your replies and time.

MJ

RBarryYoung SSC Guru Points: 143329 More actions · Answer 13

Well I do not see anything wrong with the Service Broker items. Can you execute the following script and post the results:

select * from sys.service_broker_endpoints

select * from sys.event_notifications

select * from sys.events

select * from sys.server_event_notifications

select * from sys.server_events

select * from sys.remote_service_bindings

[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

MANU-J.

SSC-Dedicated

Points: 31126

September 1, 2008 at 3:59 pm

#865758

Please find the results attached.

Thanks a lot,

MJ