Hi,
I'm trying to create a stored procedure that will send mail using the COM
interface for iMS. I took the sample SP that I received and made a couple
of changes to it. The complete text of the SP can be found below.
I added a couple of lines to capture all of the results of calling the
sendmail() method, and they indicate a problem, but I'm not sure what's
causing it.
Here's what I find in the return parameters after calling the sendmail()
method:
Result: 0
ResultString: 'Success'
StoredFileName: ''
StoredMessageSize -1
I can see from the last two parameters that there is a problem, but I'm not
sure what it is. I am providing a valid to and from address in the input
parameters, as well as a valid subject and body. The spool directory
location is correct. Each call to sp_OASetProperty and sp_OAMethod seem to
succeed (return code of 0).
Does anyone have any suggestions as to why this isn't working. I tested the
Post-SE engine using the iMS POST console and it seems to be working fine.
Thanks for any help you can give,
Bob
---- SP Code follows ----
ALTER PROCEDURE SendiMSEmail
@txtTo varchar(100),
@txtFrom varchar(100),
@txtSubject varchar(100),
@txtBody varchar(1000)
/* Stored Procedure: Send_email
Author: Tom Langer - [EMAIL PROTECTED]
This sp sends an email message using the IMSMail COM Object - it places
the email message directly in the email message queue used
by the inFusion Mail Server. I am using this in a real application,
however, my program doesn't need so many headers/variables to be support,
so I didn't create a super delux procedure here.
INPUTS:
If you can't figure them out, email me :-)
OUTPUTS:
The stored procedure returns a single record recordset, containing 1
field called Result, which will have only these values:
The value is either FAILURE or SUCCESSFUL
If successful, it may indicate whether or not there was an error or not
"FAILURE - Object could not be created'
"Send mail successful"
"Send mail successful with Error (..." - this result would pass what
parameters encountered an error..
*/
AS
DECLARE @RetCode int -- Return codes from OLE calls
DECLARE @iMSObject int -- Handle to iMSMail.SendMail object
DECLARE @BadRetCode int -- Keeps track of bad return codes
DECLARE @RetCodeSend int -- Resturn code from the call to SendMail
DECLARE @ErrorMsg varchar(8000) -- Keeps track of error messages
declare @tmpresult int
declare @tmpResultString varchar(5000)
declare @Filename varchar(5000)
declare @MessageSize int
EXEC @RetCode = sp_OACreate 'iMSMail.SendMail', @iMSObject OUT
-- IF THE OBJECT WAS NOT CREATED, THEN STOP THE PROCESS WITH AN ERROR
MESSAGE
IF @RetCode <> 0
BEGIN
SELECT 'FAILURE - Object could not be created' as Result
RETURN
END
SET @BadRetCode = 0
SET @ErrorMsg = ''
-- SET THE SPOOL DIRECTORY
EXEC @RetCode = sp_OASetProperty @iMSObject, 'SpoolDir', 'C:\Program
Files\iMSPOST_SE\Out'
IF (@RetCode <> 0)
BEGIN
SET @BadRetCode = @BadRetCode + @RetCode
SET @ErrorMsg = @ErrorMsg + ',Error setting Spool Directory'
END
-- SET THE SMTP TO PROPERTY.. This is who the message is really sent too
EXEC @RetCode = sp_OASetProperty @iMSObject, 'smtpto', @txtTo
IF (@RetCode <> 0)
BEGIN
SET @BadRetCode = @RetCode + @BadRetCode
SET @ErrorMsg = @ErrorMsg + ',Error setting SMTPTO'
END
-- SET THE SMTP FROM PROPERTY
EXEC @RetCode = sp_OASetProperty @iMSObject, 'smtpfrom', @txtFrom
IF (@RetCode <> 0)
BEGIN
SET @BadRetCode = @RetCode + @BadRetCode
SET @ErrorMsg = @ErrorMsg + ',Error setting SMTPFROM'
END
EXEC @RetCode = sp_OASetProperty @iMSObject, 'body', @txtBody
SET @BadRetCode = @RetCode + @BadRetCode
SET @txtTo = 'SetHeader("To", ' + char(34) + @txtTo + char(34) + ')'
SET @txtFrom = 'SetHeader("From", ' + char(34) + @txtFrom + char(34) + ')'
SET @txtSubject = 'SetHeader("Subject", ' + char(34) + @txtSubject +
char(34) + ')'
EXEC @RetCode = sp_OAMethod @iMSObject, @txtTo
EXEC @RetCode = sp_OAMethod @iMSObject, @txtFrom
EXEC @RetCode = sp_OAMethod @iMSObject, @txtSubject
EXEC @RetCodeSend = sp_OAMethod @iMSObject, 'SendMail'
EXEC @RetCode = sp_OAGetProperty @iMSObject, 'Result', @tmpResult OUT
EXEC @RetCode = sp_OAGetProperty @iMSObject, 'ResultString',
@tmpResultString OUT
EXEC @RetCode = sp_OAGetProperty @iMSObject, 'StoredFileName', @Filename OUT
EXEC @RetCode = sp_OAGetProperty @iMSObject, 'StoredMessageSize',
@MessageSize OUT
-- RETURN
--print 'sending mail is complete'
IF (@RetCodeSend <> 0)
BEGIN
SELECT 'send mail process failed' as Result
RETURN
END
IF (@RetCodeSend = 0)
BEGIN
IF (@BadRetCode = 0)
BEGIN
select 'Send mail successful' as Result
END
IF (@BadRetCode <> 0)
BEGIN
select 'Send mail successful with Error (' + @ErrorMsg + ')' as Result
END
END
-----Original Message-----
From: Howie Hamlin [mailto:[EMAIL PROTECTED]]
Sent: March 28, 2001 8:50 AM
To: inFusion Support List
Subject: Re: [iMS] RE: [ODS Support] Any Newbie Resources for iMS
Post-SE?
----- Original Message -----
From: "Bob Silverberg" <[EMAIL PROTECTED]>
To: "inFusion Support List" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Wednesday, March 28, 2001 8:10 AM
Subject: [iMS] RE: [ODS Support] Any Newbie Resources for iMS Post-SE?
> Thanks Howie. I think that should get me started. I just spent about an
> hour looking through the archives too, and picked up some info there.
>
> I'd just like to summarize my understanding, and ask for your (and/or the
> list's) confirmation and feedback:
>
> CFX_iMSMail is not really an exact replacement for CFMAIL, in that it
cannot
> produce customized emails the same way. With CFMAIL you can call the tag
> once, and produce a number of customized emails based on fields included
in
> a query. With CFX_iMSMail you need to call the tag for _each_ individual
> custom email.
>
> The best way to do the above with CFX_iMSMail is to do a CFLOOP on your
> query. Within the loop you would set a variable to the body text (which
you
> would dynamically generate), and then pass that variable to the BODY=
> attribute of the CFX_iMSMail tag.
>
Yes, this is true. A future release of iMS/cfx_imsmail will allow for
enhanced functionality. However, although cfx_imsmail is not a direct
replacement for cfmail, it does allow you to do many more things than
cfmail - and is also much faster as it puts the mail directly into the iMS
queue instead of the cf mail spool.
> Is that the best way to do it? And if so, what are the performance
> implications (i.e., is it advisable to do this if you are producing
> thousands of emails)?
>
I know of several sites that send out thousands of custom emails so I think
it works fine.
> A couple of other questions:
>
> 1. Is the current version of the CFX Tag (version 2.3, dated December 15,
> 2000) thread-safe, or should CFLOCK be used with it?
>
That version is not thread-safe but we use it here without locks and have
had no problems. There was a 2.4 version that had thread-safe code but we
had to pull it because some clients had trouble with it. We'll be retooling
the tag after iMS 2.0 is released.
> 2. Are there any known issues with running iMS Post-SE on the same machine
> as SQL Server 2000 (its use would be minimal)?
>
I don't think this is any problem.
Regards,
Howie
> Thanks again for your help,
> Bob
>
========================================================================
This list server is Powered by iMS
'The Swiss Army Knife of Mail Servers'
--------------------------------------
To leave this list please complete the form at
http://www.coolfusion.com/iMSSupport.cfm
Need an iMS Developer license? Sign up for a free license here:
http://www.coolfusion.com/iMSDevelopers.cfm
List archives: http://www.mail-archive.com/infusion-email%40eoscape.com/
========================================================================
========================================================================
This list server is Powered by iMS
'The Swiss Army Knife of Mail Servers'
--------------------------------------
To leave this list please complete the form at
http://www.coolfusion.com/iMSSupport.cfm
Need an iMS Developer license? Sign up for a free license here:
http://www.coolfusion.com/iMSDevelopers.cfm
List archives: http://www.mail-archive.com/infusion-email%40eoscape.com/
========================================================================
