[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now


Send Email using sp_send_cdosysmail through a ColdFusion page

Posted on 2004-10-28
Medium Priority
Last Modified: 2013-12-20
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">      

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?  

Question by:INFINIEDGE
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 17

Expert Comment

ID: 12439037
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      
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        
SET nocount off


Does the user on the ODBC connection have permissions to execute the sp_OASetProperty  etc...?

Author Comment

ID: 12439188
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.

Expert Comment

ID: 12440978
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?
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.


Author Comment

ID: 12443570
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.

Expert Comment

ID: 12445612
Fine with me. I just would like to know why InfiniEdge is not using <cfmail>.

Author Comment

ID: 12445734
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.

Accepted Solution

Lunchy earned 0 total points
ID: 12478872
Closed, 50 points refunded.
Friendly Neighbourhood Community Support Admin

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
This article provides a case study on how our local youth baseball league deployed a new website, including the platform selection, implementation and benefits to the league.
The purpose of this video is to demonstrate how to reset a WordPress password if you are locked out and cannot reset the password. A typical use would be if you cannot access the email to which WordPress would send the password recovery email to…
The purpose of this video is to demonstrate how to integrate Mailchimp with WordPress, by placing a Mailchimp signup form on a WordPress Page or Post. This will be demonstrated using a Windows 8 PC. Mailchimp will be used. Log into your Mailchi…

656 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