Post reply

Alerts, Emails, Net Send

  • February 16, 2007 at 3:53 pm

    #61878

    Guys,

    I have SQL Server 2000 installed on a server where, for security purposes, mail server was not installed.  I have looked for ways to set up different alerting mechanisms (e.g. upon a job failure, send an email), but the lack of the mail server created a problem.

    Recently, I found an article describing how to send emails from the SQL Server even with no mail server installed (http://www.sqlteam.com/item.asp?ItemID=5003).  I tried it.  And altough the stored procedure compiled and executed with no problems, no email message has been sent.  I still cannot figure out why.

    Today I learned that we can use 'NET SEND' to achieve at least partially what I wanted.  Namely, I can set up the operator and have a job be binded to the operator, so that if a job fails, a pop-up message comes up.  Although it is a work-around, it is not the best one, as I do not know how to get those messages on my blackberry (which I could, would I have the ability to send emails).

    Questions:

    1. Does anyone know any other working way of sending email alerts when no email server is installed?

    2. Does anyone know whether it is possible to somehow capture net-send message and fire up an email?

    Any suggestion is greatly appreciated!

    Thanks

  • February 17, 2007 at 11:58 pm

    #690369

    How to send e-mail without using SQL Mail in SQL Server

    http://support.microsoft.com/kb/312839

    SQL Server SMTP Mail XP

    http://www.sqldev.net/xp/xpsmtp.htm

     

    MohammedU
    Microsoft SQL Server MVP

  • February 19, 2007 at 9:09 am

    #690505

    To send emails you do need to have SMTP server somewhere on your network. Then you can use vbscripts using CDO to send email alerts.

  • February 20, 2007 at 3:14 am

    #690603

    you will also need http://www.dbmaint.com/free_utilities.asp if you install XPSMTP, it works really well, SMTP is a much more reliable than MAPI. If you have an IIS machine on your network you can forward all the mail through that.

  • February 20, 2007 at 8:59 am

    #690684

    Guys,

    Thanks for all the replies.

    I actually have the mail working on my local server, to which all the other production servers are linked.

    My latest approach is to just create a table on each of those servers, which will store the alert info.  This table will be queried every 5 minutes, by a job sitting on my local machine, and upon any new entry into the table, it will send me an email (i.e. from my local machine).

    The table will be populated by a stored procedure, which will be executed upon a certain alert, which I'll have to set up.

     

    Any suggestions/objections on/to this new approach?

     

    Thank you

  • February 21, 2007 at 11:19 am

    #690987

    sql_er

    a very interesting approach.

    can you provide more details? which sp you use to populate alert table?

    thanks

  • February 28, 2007 at 12:38 pm

    #692396

    Ken,

    More clear details are below:

    Production Sever

    ****************

    a. Table to store the errors

    b. A stored procedure to insert into the error table

    c. An alert, which will execute the stored procedure, which will insert into the error table

    Local Server

    ************

    a. A stored procedure, which looks into the 'production' Error table and sends an email if there is any new activity

    b. Job running every 5 mins and executing the stored procedure mentioned in a.

     

    As for the INSERT stored procedure, just create a simple one.  All it does is an insert whenever executed.  Something like this:

    CREATE PROCEDURE InsertIntoErrorTable

    AS

    BEGIN

    INSERT INTO Errors(Name) VALUES("This type of error happened")

    END

Viewing 7 posts - 1 through 6 (of 6 total)

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