Send Email using sp_send_cdosysmail through a ColdFusion page

I'm trying to send email using the stored procedure called sp_send_cdosysmail through a coldfusion page.  The stored procedure can be found here http://support.microsoft.com/default.aspx?scid=kb;en-us;312839&sd=tech under the section "Create a Stored Procedure to Send CDOSYS Mail".  I'm passing in the required variables From, To, Subject, and Body.  If I run the query thorugh query analyzer the mail gets sent.  If I call the stored procedure through the webpage it is not sent.  Here's my code for calling the procedure.  The stored procedure is in the Master database.

<cfstoredproc procedure="sp_send_cdosysmailHTML" datasource="#datasource#">
      <cfprocparam cfsqltype="cf_sql_varchar" type="in" value="person@foo.com" dbvarname="@To">
      <cfprocparam cfsqltype="cf_sql_varchar" type="in" value="orders@foo.com" dbvarname="@From">
      <cfprocparam cfsqltype="cf_sql_varchar" type="in" value="Order Confirmation" dbvarname="@Subject">
      <cfprocparam cfsqltype="cf_sql_longvarchar" type="in" value="This is the body of the email" dbvarname="@Body">      
</cfstoredproc>

When I call the procedure from the website I don't get an error message.  I've used an output variable to make sure the values where being passed in and the result were the values that were passed in.  

Can anyone shed some light on this situation?  

Thanks
INFINIEDGEAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Tacobell777Commented:
You dont get an error, so I am assuming it just doesn't work?

Can you run it from Query Analyzer?

Does the following work if you run it? (I know it works on my machine)

ALTER Procedure sp_SMTPMail    
@SenderName varchar(100),      
@SenderAddress varchar(100),    
@RecipientName varchar(100),    
@RecipientAddress varchar(100),
@Subject varchar(200),  
@Body varchar(8000)AS          

SET nocount on          
declare @oMail int --Object reference  
declare @resultcode int        
EXEC @resultcode = sp_OACreate 'CDONTS.NewMail', @oMail OUT            
if @resultcode = 0      
BEGIN          
EXEC @resultcode = sp_OASetProperty @oMail, 'From', @SenderAddress              
EXEC @resultcode = sp_OASetProperty @oMail, 'To', @RecipientAddress            
EXEC @resultcode = sp_OASetProperty @oMail, 'Subject', @Subject        
EXEC @resultcode = sp_OASetProperty @oMail, 'Body', @Body                      
EXEC @resultcode = sp_OAMethod @oMail, 'Send', NULL                    
EXEC sp_OADestroy @oMail        
END            
SET nocount off

GO

Does the user on the ODBC connection have permissions to execute the sp_OASetProperty  etc...?
0
INFINIEDGEAuthor Commented:
I tried your procedure and I still didn't get any results or an error.  I can run both of the stored procedures through query analyzer with no problem.  In query analyzer the email is sent out and I recieve the email.

I checked the permissions for sp_OASetProperty, sp_OAMethod, etc and my user is granted permission to execute those procedures.
0
sigmaconCommented:
1) The user with which ColdFusion is running needs to have the appropriate permissions.
2) Why are you not using <cfmail> instead - that would be much easier. What is the requirement that forces you to use SQL Server to send emails?
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

INFINIEDGEAuthor Commented:
I solved the problem.  Apparently the <cfprocparam> tag's dbvariable doesn't work as I had hoped.  I assigned the variables in a different order than the stored proc was looking for them.  I thought by setting the dbvariable to the stored procedure's variable name the procedure wouldn't care the order of the parameters, this wasn't the case.

Thanks to all who responded.
0
sigmaconCommented:
Fine with me. I just would like to know why InfiniEdge is not using <cfmail>.
0
INFINIEDGEAuthor Commented:
Hi sigmacon,

I am currently in the process of changing the site from ColdFusion to ASP.net.  For a smooth transition I wanted to send my emails through a stored procedure that is already sending out ASP.net emails for my company.

I am not re-writing the current page in ASP.net, it will be done in the near future.  I was hoping that by using a stored procedure instead of the <cfmail> tag I would be able to minimize my work when I do write the page in ASP.net.
0
LunchyCommented:
Closed, 50 points refunded.
Lunchy
Friendly Neighbourhood Community Support Admin
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.