Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL AGENT Alert on Logon Trigger Raiserror Expand / Collapse
Author
Message
Posted Tuesday, August 10, 2010 4:27 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 12, 2010 8:36 AM
Points: 7, 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.
Post #967056
Posted Wednesday, August 11, 2010 3:10 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 12, 2010 8:36 AM
Points: 7, 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.
Post #967842
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse