Return code from SendMail sp_OAMethod

Posted on 2003-03-24
Medium Priority
Last Modified: 2012-06-27
I have a stored procedure that sends an email using an SMTP Mailer COM Component, ASPmail.  It works fine but I want to know if there is a way to retreive the return code if the RecipientAddress is not valid. Here's a portion of the SP that sends the email:

ALTER    Procedure PDM_sp_SMTPMail

EXEC @resultcode = sp_OAMethod @oMail, 'SendMail', NULL
          if @resultcode <> 0
               return -1

more code

I figured I could get the return code this way but it seems it's always equal to zero no matter what the Recipient email address is.
Any suggestions will be most appreciated.

Question by:marshallgarza
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 32

Accepted Solution

Brendt Hess earned 300 total points
ID: 8198831
Well, the emailer may not know what the result of the send attempt is.  However, it may. and may be returning that value in the ReturnCode.  From the docs:

_OAMethod objecttoken,
    [, returnvalue OUTPUT]
    [, [@parametername =] parameter [OUTPUT]

returnvalue OUTPUT
Is the return value of the method of the OLE object. If specified, it must be a local variable of the appropriate data type.
If the method returns a single value, either specify a local variable for returnvalue, which returns the method return value in the local variable, or do not specify returnvalue, which returns the method return value to the client as a single-column, single-row result set.

If the method return value is an OLE object, returnvalue must be a local variable of data type int. An object token is stored in the local variable, and this object token can be used with other OLE Automation stored procedures.

When the method return value is an array, if returnvalue is specified, it is set to NULL.

An error occurs when:

    returnvalue is specified, but the method does not return a value.
    The method returns an array with more than two dimensions.
    The method returns an array as an output parameter.

So, using your example:

EXEC @hr = sp_OAMethod @oMail, 'SendMail', @resultcode OUT
         if @resultcode <> 0 or @hr <> 0
              return -1

more code

Expert Comment

ID: 9275557
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
Post your closing recommendations!  No comment means you don't care.

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

770 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