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 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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply