Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2003
    Location
    Frisco, TX
    Posts
    21

    Question Unanswered: Can you send an E-mail notification when a table gets updated?

    I created a couple stored procedures to execute from local, to a remote, and then back to the local site to test responsiveness. When the time it takes for the round trip goes above a certain threshold, I update a table that holds the site that is slow along with the time and date the connection was tested.

    How can I get SQL Server 2000 to send out an E-mail to a user when this table gets a new row?

    I would think that you would want to add a trigger looking for an insert into that table, but can you send out an E-mail notification from a trigger? If not, how can I rig something like this up using SQL Server?

    Thanks!

  2. #2
    Join Date
    Oct 2003
    Location
    Frisco, TX
    Posts
    21

    Got it

    Use a Trigger to call RaisError using a WITH LOG option.

    Then create an Alert that looks for the text you used in RaisError

  3. #3
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621

    Re: Got it

    Originally posted by darrinps
    Use a Trigger to call RaisError using a WITH LOG option.

    Then create an Alert that looks for the text you used in RaisError
    Or just send email from trigger directly (xp_sendmail).

  4. #4
    Join Date
    Oct 2003
    Location
    Frisco, TX
    Posts
    21

    Re: Got it

    Originally posted by snail
    Or just send email from trigger directly (xp_sendmail).
    Doh!

    Now you tell me!


    I'll give that a try.

    Thanks.

  5. #5
    Join Date
    Oct 2003
    Location
    Frisco, TX
    Posts
    21

    Re: Got it

    Originally posted by snail
    Or just send email from trigger directly (xp_sendmail).
    That doesn't seem to work for me.

    It says that:
    "Either there is no default mail client or the current mail client cannot fulfill the messaging request. Please run Microsoft Outlook and set it as the default mail client."

    Since we don't want to run MS Outlook all the time, I tried using BLAT, but I can't get SQL Server to execute the command line (at least it doesn't seem to) when the Alert gets activated, so I'm posting a question about that to see if there is some trick to it.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Re: Got it

    How about...

    master..xp_cmdshell 'your command line code'
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Sep 2003
    Posts
    522
    make sure you sql service is running under a domain account.

  8. #8
    Join Date
    Sep 2003
    Posts
    522
    ooops, the previous posting was for Brett Kaiser's suggestion.

    as to xp_sendmail, - use cdo instead, this way you won't have to run any mapi client in order to send mail. search for cdo and cdonts on google, you'll find tons of it. another way (requires installation of extended stored procedure) - search for xp_smtp_sendmail, also no client mapi required.

  9. #9
    Join Date
    Oct 2003
    Location
    Frisco, TX
    Posts
    21

    Re: Got it

    Originally posted by Brett Kaiser
    How about...

    master..xp_cmdshell 'your command line code'
    Grrrrr....

    That shoould have worked but it doesn't.

    Here is what I have:

    CREATE TRIGGER SlowResponsesTrigger
    ON [dbo].[SlowResponses]
    FOR INSERT
    AS

    RAISERROR('Slow response', 10, 1) WITH LOG

    EXEC master..xp_cmdshell 'C:\blat\blat.exe StandardMessage.txt -to Test.Test@MyURL.com -s "Slow Connection"'


    Now, if I copy and paste:

    'C:\blat\blat.exe StandardMessage.txt -to Test.Test@MyURL.com -s "Slow Connection"'

    to the command line, it works...it just won't work when being called from SQL.

    BUT, when I type it into the SQL Query Analyser, I get this:

    "The system cannot find the path specified."

    Huh? Do I need to set something for it to see my local hard drive?

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Re: Got it

    Hard drive of the server, not your local machine...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Oct 2003
    Location
    Frisco, TX
    Posts
    21

    Re: Got it

    Originally posted by Brett Kaiser
    Hard drive of the server, not your local machine...
    So instead of:

    EXEC master..xp_cmdshell 'C:\blat\blat.exe StandardMessage.txt -to Test.Test@MyURL.com -s "Slow Connection"'

    I would need to put something in front of the C:\blat.... to redirect it to my machine, but what would the syntax be? Is it my machine name?

    Thanks.
    Last edited by darrinps; 10-20-03 at 18:19.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •