Mail alert from SQL 2K Std. Ed.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Mail alert from SQL 2K Std. Ed.

Author

Topic

PatDeV
Posting Yak Master

197 Posts

Posted - 2006-06-02 : 11:08:33

Hi all,

I have one server that is on SQL 2000 Std edition. I want to get email from server if the job fails or something else fails. But there is no SQL mail running also. How can i do that to get message for the job fail!!

Thanks

mfemenel
Professor Frink

1421 Posts

Posted - 2006-06-02 : 12:35:36

Since our fine prod support folks have left sql mail broken for over a year we've coded around them and use CDO instead.
Create PROCEDURE p_TextMsgSend
@From varchar(100),
@To varchar(500),
@Subject varchar(250),
@Body varchar(4000),
@attachment1 nvarchar(500)=NULL,
@attachment2 nvarchar(500)=NULL
AS
DECLARE @MailMsg int
DECLARE @Config int
DECLARE @Flds int
DECLARE @hr int
DECLARE @ret int
Declare @att int

set @ret = 0

EXEC @hr = sp_OACreate 'CDO.Message', @MailMsg OUT

IF @hr <> 0
EXEC p_OAErrorInfo @MailMsg, @hr, 'sp_OACreate 1'

EXEC @hr = sp_OASetProperty @MailMsg, 'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
IF @hr <> 0
EXEC p_OAErrorInfo @MailMsg, @hr, 'sp_OASetProperty 2'

EXEC @hr = sp_OASetProperty @MailMsg, 'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', 'mail.nextel.com'
IF @hr <> 0
EXEC p_OAErrorInfo @MailMsg, @hr, 'sp_OASetProperty 3'

EXEC @hr = sp_OASetProperty @MailMsg, 'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserverport").Value', '25'
IF @hr <> 0
EXEC p_OAErrorInfo @MailMsg, @hr, 'sp_OASetProperty 4'

EXEC @hr = sp_OAMethod @MailMsg, 'Configuration.Fields.Update', NULL
IF @hr <> 0
EXEC p_OAErrorInfo @MailMsg, @hr, 'sp_OAMethod 5'

EXEC @hr = sp_OASetProperty @MailMsg, 'From', @From
IF @hr <> 0
EXEC p_OAErrorInfo @MailMsg, @hr, 'sp_OASetProperty 8'

EXEC @hr = sp_OASetProperty @MailMsg, 'To', @To
IF @hr <> 0
EXEC p_OAErrorInfo @MailMsg, @hr, 'sp_OASetProperty 9'

EXEC @hr = sp_OASetProperty @MailMsg, 'Subject', @Subject
IF @hr <> 0
EXEC p_OAErrorInfo @MailMsg, @hr, 'sp_OASetProperty 10'

EXEC @hr = sp_OASetProperty @MailMsg, 'TextBody', @Body
IF @hr <> 0
EXEC p_OAErrorInfo @MailMsg, @hr, 'sp_OASetProperty 11'

if (@attachment1 is not null)
BEGIN
EXEC @att= sp_OAMethod @MailMsg, 'AddAttachment',null,@attachment1
END

if (@attachment2 is not null)
BEGIN
EXEC @att= sp_OAMethod @MailMsg, 'AddAttachment',null,@attachment2
END

EXEC @ret = sp_OAMethod @MailMsg, 'Send'
IF @ret <> 0
EXEC p_OAErrorInfo @MailMsg, @ret, 'sp_OAMethod 13'

EXEC @hr = sp_OADestroy @MailMsg
IF @hr <> 0
EXEC p_OAErrorInfo @MailMsg, @hr, 'sp_OADestroy 14'

set @ret = isNull(@ret,0)

RETURN @ret

GO

Mike
"oh, that monkey is going to pay"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources