April 9, 2009 at 10:13 am
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
April 9, 2009 at 12:15 pm
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
April 15, 2009 at 3:05 pm
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
April 16, 2009 at 10:45 am
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