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
      begin
            execute TRP.sp_FFS_Datasource_Recent_Risk
            PRINT 'Executed TRP.sp_FFS_Datasource_Recent_Risk'
      end

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

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

http://database.ittoolbox.com/documents/setting-up-sql-mail-with-lotus-notes-email-system-18118

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

Regards,
SelvaS
0
 
Sjef BosmanGroupware ConsultantCommented:
It might be (a lot) easier to send an SMTP mail through the Domino server.

http://www.sqlteam.com/article/sending-smtp-mail-using-a-stored-procedure
0
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'

      AS      
      
      SET nocount on
      
      declare @oMail int --Object reference
      declare @resultcode int
      
      EXEC @resultcode = sp_OACreate 'SMTPsvg.Mailer', @oMail OUT
      
      if @resultcode = 0
      BEGIN
            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
      END      
      

      SET nocount off

I called the procedure with this:

exec TRP.sp_Q_SMTPMail @SenderName='Quynh Nguyen', @SenderAddress='quynh.nguyen@healthnet.com',
@RecipientName = 'Quynh Nguyen', @RecipientAddress = 'qnryat@pacbell.net',
@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.
0
 
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.
0
 
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?
0
 
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.
0
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.