SP works until called from ASP

Question

Post reply

SP works until called from ASP

rschaeferhig

SSCrazy Eights

Points: 8776
More actions
November 30, 2004 at 11:25 am

#84939

I have a copy of SP_Send_CDOSysMail I cribbed from the scripts area here. It has been modified to send from a specific user and to use HTML formatting. I run the script from Query Analyzer and it works just fine. I get an HTML formatted email from the correct sender with the correct account. When I execute the SP from an ASP/VBScript page I get no error messages, an RC of 0, but I never get an email.
Can anyone shed a light on this? I'll include a copy of the SP and the ASP code below.
Thanks.
SP ----------------------------------------------------------------------
CREATE PROCEDURE [dbo].[sp_send_cdosysmail]
(
-- @From varchar(100) ,
-- @To varchar(100) ,
 @BCC varchar(8000),
 @Subject varchar(200)='',
 @Body varchar(8000) =''
)
/*********************************************************************
This stored procedure takes the parameters and sends an e-mail.
All the mail configurations are hard-coded in the stored procedure.
Comments are added to the stored procedure where necessary.
References to the CDOSYS objects are at the following MSDN Web site:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_messaging.asp
***********************************************************************/
 AS
 Declare @iMsg int
 Declare @hr int
 Declare @rc int
 Declare @source varchar(255)
 Declare @description varchar(500)
 Declare @output varchar(1000)

 SET NOCOUNT ON
--************* Create the CDO.Message Object ************************
 EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT
--***************Configuring the Message Object ******************
-- This is to configure a remote SMTP server.
-- http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_schema_configuration_sendusing.asp
 EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields(".Value','2'">http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
-- This is to configure the Server Name or IP address.
-- Replace MailServerName by the name or IP of your SMTP Server.
 EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields(".Value'">http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', 'higmx.thehartford.com'
-- Save the configurations to the message object.
 EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null
-- Set the e-mail parameters.
 EXEC @hr = sp_OASetProperty @iMsg, 'To', '"The Hartford Today" <'">TheHartfordToday@thehartford.com>'
 EXEC @hr = sp_OASetProperty @iMsg, 'From', '"The Hartford Today" <'">TheHartfordToday@thehartford.com>'
 EXEC @hr = sp_OASetProperty @iMsg, 'BCC', @BCC
 EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject
-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
 EXEC @hr = sp_OASetProperty @iMsg, 'HTMLBody', @Body
 EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL
 SET @rc = @hr
-- Sample error handling.
 IF @rc <>0 BEGIN
EXEC @hr = sp_OADestroy @iMsg
RETURN @rc
 END
 EXEC @hr = sp_OADestroy @iMsg
 RETURN 0
GO
ASP ---------------------------------------------------------------
Dim cmdStoredProc2
Set cmdStoredProc2 = Server.CreateObject("ADODB.Command")
cmdStoredProc2.ActiveConnection = dbConn
' Define the stored procedure inputs and outputs. Question marks act as placeholders for each parameter for the stored procedure' cmdStoredProc2.CommandText = "sp_send_cdosysmail" cmdStoredProc2.CommandType = adcmdStoredProc
Response.Write("BCC: "&strBcc&" ")
Response.Write("Title: "&strTitle&" ")
Response.Write("BLen: "&cstr(len(strBody))&" ")
Response.Write("HTML: "&strbody&"***END HTML***" ") cmdStoredProc2.Parameters.Append cmdStoredProc2.CreateParameter("RETURN_VALUE", adInteger, adParamReturnValue) cmdStoredProc2.Parameters.Append cmdStoredProc2.CreateParameter("@Bcc", advarChar, adParamInput, 8000, strBcc) cmdStoredProc2.Parameters.Append cmdStoredProc2.CreateParameter("@Subject", advarchar, adParamInput, 200, strTitle) cmdStoredProc2.Parameters.Append cmdStoredProc2.CreateParameter("@Body", advarchar, adParamInput, 8000, strbody)

On Error Resume Next

cmdStoredProc2.Execute, , adExecuteNoRecords

interr=err.number
if interr>0 then Response.Write("Error executing mail procedure. ") Response.Write("Code: "&cstr(interr)&" ")
Response.Write("Text: "&cstr(err.Description)&" ")
end if
on error goto 0
intrc=cmdStoredProc2.Parameters("RETURN_VALUE")
if intrc=0 then
 Response.Write("RC: 0 ")
else
 Response.Write("RC: "&cstr(intrc)&" ")
end if
set cmdStoredProc2=nothing
dbconn.Close
set dbconn=nothing
Response.flush

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

The problem comes from ASP :

every Constants must be converted to their respective value.

ex : cmdStoredProc2.CommandType = adcmdStoredProc

== : cmdStoredProc2.CommandType = 4

this should solve your problem. If that still doesn't work then you may have a permission issue between the asp user and the SP.

rschaeferhig SSCrazy Eights Points: 8776 More actions · Answer 2

cmdStoredProc2.CommandType = adcmdStoredProc works just fine assuming you include adovbs.inc.

It was the damned SQL permissions. The DBA didn't set up the userid the way I'd asked. Once I got the permissions right the SP worked like a charm.

One of those things that's right in front of you but you can't see it because you're too close.

Thanks for the help.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Happy to help... how do you include the adovbs.inc file exactly? I haven't worked for a while in asp but I could surely use that info eventually...

rschaeferhig SSCrazy Eights Points: 8776 More actions · Answer 4

"..path/" is the path to the file. If the file is in the same directory as your ASP page you don't need the path info.

Thanks again for your help.