Send Lotus Notes email from SQL Server 2005

How do I send an automated Lotus Notes email from SQL Server 2005?

-- execute projection if pass validation
-- send notification email if not pass validation

declare @intCheckCount smallint;

set @intCheckCount = (select sum(Rec_count) from TRP.Validation_check where source = 'ffs')

if @intCheckCount is null
            execute TRP.sp_FFS_Datasource_Recent_Risk
            PRINT 'Executed TRP.sp_FFS_Datasource_Recent_Risk'

if @intCheckCount >= 1
            execute TRP.Send_mail
                PRINT 'Check TRP.Validation_check'

Can someone please help me to create a store procedure (name: TRP.Send_mail) to send the notification email?
Who is Participating?
Please refer the below link

This will help you to configure sql mail for lotus notes.

Sjef BosmanGroupware ConsultantCommented:
It might be (a lot) easier to send an SMTP mail through the Domino server.
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

HNA071252Author Commented:
I created the store procedure called "sp_Q_SMTPMail"

ALTER Procedure [TRP].[sp_Q_SMTPMail]

      @SenderName varchar(100),
      @SenderAddress varchar(100),
      @RecipientName varchar(100),
      @RecipientAddress varchar(100),
      @Subject varchar(200),
      @Body varchar(8000),
      @MailServer varchar(100) = 'localhost'

      SET nocount on
      declare @oMail int --Object reference
      declare @resultcode int
      EXEC @resultcode = sp_OACreate 'SMTPsvg.Mailer', @oMail OUT
      if @resultcode = 0
            EXEC @resultcode = sp_OASetProperty @oMail, 'RemoteHost', @mailserver
            EXEC @resultcode = sp_OASetProperty @oMail, 'FromName', @SenderName
            EXEC @resultcode = sp_OASetProperty @oMail, 'FromAddress', @SenderAddress
            EXEC @resultcode = sp_OAMethod @oMail, 'AddRecipient', NULL, @RecipientName, @RecipientAddress
            EXEC @resultcode = sp_OASetProperty @oMail, 'Subject', @Subject
            EXEC @resultcode = sp_OASetProperty @oMail, 'BodyText', @Body
            EXEC @resultcode = sp_OAMethod @oMail, 'SendMail', NULL
            EXEC sp_OADestroy @oMail

      SET nocount off

I called the procedure with this:

exec TRP.sp_Q_SMTPMail @SenderName='Quynh Nguyen', @SenderAddress='',
@RecipientName = 'Quynh Nguyen', @RecipientAddress = '',
@Subject='SQL Test', @body='Hello, this is a test email from SQL Server'

When I ran this, it said "Command(s) completed successfully." but I didn't get any email. Please let me know what other configurations I need to do.
Sjef BosmanGroupware ConsultantCommented:
Any logging available? Can you debug the code somehow? Or spice it up with some trace calls?

If SQL-server and Domino are not the on the same machine, you'd have to modify the line with
      @MailServer varchar(100) = 'localhost'
to indicate the correct server, using either its DNS name or an IP-address.
HNA071252Author Commented:
Yes, the server is on a different machine. I modified the @MailServer varchar(100) to equal the actual ip address of the server machine. I executed again  it said "Command(s) completed successfully." but I still didn't get any email. How can I debug or see the log in?
Sjef BosmanGroupware ConsultantCommented:
What you could check first is the server log of the Domino server, it might have refused your mail. You'll probably need the cooperation of an administrator of the Domino environment, who has access to the log.nsf database. That log contains info about all mails sent to and received by the Domino server, as well as the attempted mails.

> I modified the @MailServer varchar(100) to equal the actual ip address of the server machine.
Make sure the "server machine" is the destination server, the Domino server.

There is no log in, it's just an incoming SMTP mail message. The message could be refused by the mail server, for several reasons, but the log should tell you that. The most important thing at the moment is to find out whether the SQL server contacted the Domino server, that information can be found in the log on the Domino server.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.