SQLAgentMail with Lotus Notes

Question

SQLAgentMail with Lotus Notes

RSingh-135921

Default port

Points: 1438
More actions
July 5, 2007 at 8:30 am

#175203

Hi,
We have the following configuration for our SQL Box:
SQL Server: Microsoft SQL Server 2000 - 8.00.2039
Edition: Enterprise Edition
OS: Windows Server 2003 Standard Edition
Both SQLServer and SQLServerAgent are running under a domain account that has administrative rights on this box.
Also we are using IBM Lotus Notes for mail exchanges.
Is it possible to configure SQL Mail and SQLAgent mail to be used with Lotus Notes for error notifications.
I tried searching on Google but couldn't find any helpful links.
Has anyone done this before?
Pls help.
Regards,
RSINGH

Viewing 10 posts - 1 through 9 (of 9 total)

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

Gordon Pollokoff SSCommitted Points: 1930 More actions · Answer 1

I've never been able to get SQLMail to work successfully with Lotus Notes. Instead, I have had to resort to using VBScript and CDONTS to send mail.

Gordon Pollokoff

"Wile E. is my reality, Bugs Bunny is my goal" - Chuck Jones

philcart SSC-Forever Points: 47794 More actions · Answer 2

Another option for email is XPSMTP available from http://www.sqldev.net

--------------------
Colt 45 - the original point and click interface

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 3

You can also use the CDO objects to send mails. The procedure script is avaiable in this site and that can be used.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

vadba SSChampion Points: 11133 More actions · Answer 4

I work for a government agency that uses Lotus Notes / Domino.

I actually got email working just now in about 15 minutes using the extended stored procedure XPSMTP mentioned above.

The only part I initially wasn't sure about was identifying the SMTP server used to relay Notes email to the Internet.

What I did was to send an email from my work email to my private email. In Outlook Express (my home email client), I right-clicked on the message, selected Properties from the popup menu, then the Details tab. The top portion of the details looks like this (with some XXX's in place of real info):

Received: from mail.myorg.gov ([xxx.xxx.xxx.xxx])

by xxx.mailsrvcs.net (Sun Java System Messaging Server 6.2-6.01 (built Apr

3 2006)) with ESMTP id for

mypersonalemail@verizon.net; Fri, 06 Jul 2007 08:34:46 -0500 (CDT)

Received: from unknown (HELO xxx.myorg.gov) ([172.27.83.106])

by mail.myorg.gov with ESMTP; Fri, 06 Jul 2007 09:34:44 -0400

I then used "mail.myorg.gov" as the SMTP server in the sample queries. Lo and behold, an email appeared in my personal email.

(I first verified it would work using the PING example).

One thing to note is that this won't work directly for SQL Agent notifications (as stated in the XPSMTP documentation), but there may be a workaround using code developed by still another person. Read the XPSMTP documentation thoroughly if you try that route.

Good luck.

[EDIT]

P.S.

These are the steps I used:

1. Copied XPSMTP80.DLL to C:\Program Files\Microsoft SQL Server\MSSQL\Binn

2. Executed:

exec sp_addextendedproc 'xp_smtp_sendmail', 'xpsmtp80.dll'

grant execute on xp_smtp_sendmail to public

3. After identifying the possible SMTP server in my organization as mentioned above, I ran this to ping the smtp server:

declare @rc int

exec @rc = master.dbo.xp_smtp_sendmail @server = N'xxx.myorg.gov', @ping = 1

select @rc

This returned 0, indicating success (valid SMTP server).

4. I then sent an email with code like this:

declare @rc int

exec @rc = master.dbo.xp_smtp_sendmail

@FROM = N'myworkemail@myorg.gov',

@FROM_NAME = N'TEST',

@TO = N'mypersonalemail@verizon.net',

@subject = N'SQL Server SMTP Mail',

@message = N'Test message body using XPSMTP from SQL Server',

@type = N'text/plain',

@server = N'xxx.myorg.gov'

select RC = @rc

go

RSingh-135921 Default port Points: 1438 More actions · Answer 5

Thanks everyone.

I used the SP mentioned in this article: How to send e-mail without using SQL Mail in SQL Server.

I created an SP that checks sysjobhistory and sysjobs for the jobs that have failed in the last N mins and then sends a notification (using the SP mentioned in the MS article) to a list of people. Then I scheduled this SP as a job in SQL Server Agent to run every N mins and it works like a charm now !

Regards

philcart SSC-Forever Points: 47794 More actions · Answer 6

Glad you found a solution that worked for you. Just be aware that the sp_OA* procedures used in that article are notrious for memory leaks.

--------------------
Colt 45 - the original point and click interface

Michael Meierruth SSChampion Points: 10051 More actions · Answer 7

Gordon, can you show some sample VBScript code for sending email to an smtp server. Thanks loads.

Gordon Pollokoff SSCommitted Points: 1930 More actions · Answer 8

Dim objMessage

Dim bodytext

'Variable to hold message body

bodytext = "This is some text." & vbcrlf & This is another line of text." & vbcrlf

Set objMessage = CreateObject("CDO.Message")

objMessage.To="recipient@mailbox.com"

'From does not have to be a legitimate address

objMessage.from="Sender@someemail.com"

objMessage.Subject="Subject Line"

objMessage.TextBody = bodytext

' 1= Local SMTP; 2= Network SMTP

objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

' Replace with IP of SMTP server

objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver")="127.0.0.1"

' Replace with PORT of SMTP server

objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25

objMessage.Configuration.Fields.Update

objMessage.Send

Gordon Pollokoff

"Wile E. is my reality, Bugs Bunny is my goal" - Chuck Jones

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 9

CDO works better and i too use script like this and have faced no problems better to use the script above.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com