Send Lotus Notes email from SQL Server 2005

Posted on 2012-08-27
Last Modified: 2012-09-04
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?
Question by:HNA071252
    LVL 9

    Assisted Solution

    Hi HAN,
    Please refer the below link

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

    LVL 46

    Assisted Solution

    by:Sjef Bosman
    It might be (a lot) easier to send an SMTP mail through the Domino server.

    Author Comment

    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.
    LVL 46

    Expert Comment

    by:Sjef Bosman
    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.

    Author Comment

    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?
    LVL 46

    Expert Comment

    by:Sjef Bosman
    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.
    LVL 46

    Accepted Solution


    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    Suggested Solutions

    There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now