asked on

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?

SOLUTION

selva_kongu

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Sjef Bosman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

HNA071252

ASKER

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.

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.

HNA071252

ASKER

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

ASKER CERTIFIED SOLUTION

Sjef Bosman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial