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

x
?
Solved

Send Email using sp_send_cdosysmail through a ColdFusion page

Posted on 2004-10-28
8
Medium Priority
?
363 Views
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">      
</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
0
Comment
Question by:INFINIEDGE
[X]
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
8 Comments
 
LVL 17

Expert Comment

by:Tacobell777
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      
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
 

Author Comment

by:INFINIEDGE
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.
0
 
LVL 8

Expert Comment

by:sigmacon
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?
0
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

by:INFINIEDGE
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.
0
 
LVL 8

Expert Comment

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

Author Comment

by:INFINIEDGE
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.
0
 
LVL 2

Accepted Solution

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

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