SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL AGENT Alert on Logon Trigger Raiserror


SQL AGENT Alert on Logon Trigger Raiserror

Author
Message
John Heimiller-433668
John Heimiller-433668
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 56
I have a Logon Trigger...fires a raiserror and I want an alert to fire when it does to send an email.

ground rules:
1. I know logon triggers that are not explicitly AFTER, fire after authentication and before connection...therefore the raiserror does not get to the USER but goes to the SQL Log. Thats OK with me. I know that.
2. I dont want an AFTER logon trigger.
3. Alert defined on all-databases does not work with this raiserror but "within-connection raiserror" does fire the Alert ...so my alert is not inherently at fault. Very simple.
4. If you think its just not possible because of #1 above...say so. Thats where I'm at....but would like to see a novel solution from you smart people out there.
5. Do not want to execute smtp sendmail from the trigger.

The thing that perplexes me is the SQL Log gets the error message .. so there must be some type of event happening.

thanks i advance,
John H.
John Heimiller-433668
John Heimiller-433668
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 56
I think I found an answer to my own question. I need to create an Event-Notification (what sql agent Alerts use under cover)...create a QUEUE,SERVICE,ROUTE, EVENT NOTIFICATION...or if not an EVENT, then Issue a message (after rollback) to the queue from my logon trigger and have the queue ACTIVATION kick off an email or something.

might also work for notifying the user with a spcific message rather then the generic trigger error that they get now when I Rollback.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search