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

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

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

Ready to get certified? Check out some courses that help you prepare for third-party exams.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

600 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