SQL 2000 Email Alerts When Jobs Fail

  • Been years since I've set up SQL Mail on a SQL 2000 server. If I remember correctly, the server would need to have the Outlook client installed on it with a profile that then SQL 2000 could use?

    Thank you.

    Rog

  • I advise to use Gerts solution on SMTP mail to avoid all the hassle with the mail client.

    Have a look at http://www.sqldev.net.

    It will off course need an extra step to send the main (failure step), but at least you don't suffer the mail client stuff.

    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

  • Hi Roger,

    Yes, You need to have a profile and outlook installed on server where you send the email from. Also you can use DTS to send the email.

    That is how I done in my SQL Production Servers.

    You can use this code -

    SQL Store Procedure Code

    ---------------------------

    CREATE PROCEDURE dbo.Send_Email_LongRunning

    AS

    SET NOCOUNT ON

    DECLARE @MaxMinutes int

    DECLARE @SQL_String varchar(2000)

    DECLARE @Value_Str varchar(2000)

    DECLARE @Spid int

    DECLARE @JobName varchar(500)

    DECLARE @ProName varchar(500)

    DECLARE @Date_Str varchar(50)

    DECLARE @Batch varchar(300)

    SET @MaxMinutes = 120

    create table #enum_job (Spid int,

    JobName varchar(500),

    ProName varchar(500),

    Date_Str varchar(50),

    Batch varchar(300))

    INSERT INTO #enum_job(Spid,JobName,ProName,Date_Str,Batch)

    SELECT p.spid,

    j.name,

    p.program_name,

    isnull(DATEDIFF(mi, p.last_batch, getdate()), 0) [MinutesRunning],

    last_batch

    FROM master..sysprocesses p

    JOIN msdb..sysjobs j ON dbo.udf_sysjobs_getprocessid(j.job_id) = substring(p.program_name,32,8)

    WHERE program_name like 'SQLAgent - TSQL JobStep (Job %'

    AND isnull(DATEDIFF(mi, p.last_batch, getdate()), 0) > @MaxMinutes

    SELECTTOP 1 @Spid = Spid,

    @JobName = JobName,

    @ProName = ProName,

    @Date_Str = Date_Str,

    @Batch = Batch

    FROM#enum_job

    SELECT @Value_Str = ' '

    SELECT @Value_Str = @Spid + @JobName + @ProName + @Batch

    If Len(LTrim(RTrim(@Value_Str))) > 0

    Begin

    SELECT @SQL_String = 'c:\SendMail.exe' + ' ' + 'Jobs Running Over 2 hours' + ',' + @Value_Str

    exec master..xp_cmdshell @SQL_String, no_output

    End

    Drop table #enum_job

    GO

    Change @MaxMinute to any time interval as you like.

    Then I am using VB code to send the email from @Value_Str.

    Let me know if you need my VB code as well. Good Luck.

    Leo

  • Just want to second ALZDBA. I have found Gerts solution to be far more reliable on SQL 2000.

Viewing 4 posts - 1 through 4 (of 4 total)

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