CDOSYS mail failing - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

CDOSYS mail failing

Author

Topic

scottpt
Posting Yak Master

186 Posts

Posted - 2005-05-24 : 12:26:02

Has anyone seen this?

Error:

sp_OAMethod ERROR
-----------------
-2147220991

(1 row(s) affected)

Description: Exception 0xc0000005 was generated at address 0x15e2aeef

Proc:

ALTER PROCEDURE sp_send_cdosysmail
@From varchar(100) ,
@To varchar(100) ,
@Subject varchar(100)=' ',
@Body varchar(4000) =' ',
@attachments varchar(4000)=NULL,
@query varchar(1000)=null
/*********************************************************************

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,
@hr int,
@source varchar(255),
@description varchar(500),
@output varchar(1000),
@execstr varchar(1000),
@X int,
@Length int,
@start int,
@filename varchar(255)

--************* 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("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("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', 'mailhost.bankone.net'

-- 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', @To
EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject

-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body

IF @query is not null
begin
Set @execstr='osql -S' + rtrim(@@servername) + ' -E -w3000 -Q"' + rtrim(ltrim(@query)) + '" -o"c:\queryresults.txt"'
exec master..xp_cmdshell @execstr
--print @execstr
EXEC @hr = sp_OAMethod @iMsg, 'AddAttachment',NULL, 'c:\queryresults.txt'
exec master..xp_cmdshell 'del /q c:\queryresults.txt'
end

IF @attachments IS NOT NULL
BEGIN
set @Start=1
while @Start <=len(@attachments)
BEGIN
Set @X=Charindex(',',@attachments,@Start)
If @X=0
Begin
Set @filename=SUBSTRING(@attachments,@Start,((len(@attachments)-@start)+1) )
Set @Start=len(@attachments)+1
end
Else
begin
Set @filename=SUBSTRING(@attachments,@Start,(@X-@start) )
Set @Start=@X+1
end
--print @filename
set @filename=rtrim(ltrim(@filename))
EXEC @hr = sp_OAMethod @iMsg, 'AddAttachment',NULL, @filename
END
END

EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL

-- Sample error handling.
IF @hr <>0
select @hr 'sp_OAMethod ERROR'
BEGIN
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END

-- Do some error handling after each step if you have to.
-- Clean up the objects created.
EXEC @hr = sp_OADestroy @iMsg

GO

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-05-27 : 00:52:46

I had this kind of error when I'm using ActiveX Scripts. Mostly it will be security settings. I think the error is due to user who might not have sysadmin permissions.

Hope somebody will be able help you more on this.

Karunakaran
___________
It's better to be loved and lost, than ever to be loved...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-05-27 : 04:39:07

See if this helps you

CREATE PROCEDURE SendMail(
@From varchar(255),
@To varchar(255),
@Message varchar(8000),
@Subject varchar(255))
AS

DECLARE @CDO int, @OLEResult int, @Out int

--Create CDONTS.NewMail object
EXECUTE @OLEResult = sp_OACreate 'CDONTS.NewMail', @CDO OUT
IF @OLEResult <> 0 PRINT 'CDONTS.NewMail'

EXECUTE @OLEResult = sp_OASetProperty @CDO, 'BodyFormat', 0
EXECUTE @OLEResult = sp_OASetProperty @CDO, 'MailFormat', 0

--Call Send method of the object
execute @OLEResult = sp_OAMethod @CDO, 'Send', Null, @From, @To, @Subject, @Message, 1 --0 is low 1 is normal
IF @OLEResult <> 0 PRINT 'Send'

--Destroy CDO
EXECUTE @OLEResult = sp_OADestroy @CDO

return @OLEResult

Madhivanan

Failing to plan is Planning to fail

twostepted
Starting Member

1 Post

Posted - 2006-03-31 : 16:10:44

I'm having the same problem. Can anyone help with diagnosing this problem. We've been using CDO mail for a while with no problems and have only noticed the problem recently. And I'm sure that its not because of the addition of the CC field. I've noticed the same problem with our TextBody stored proc too.

Our HtmlBody mailing stored proc is below. The function dbo.handleOAError() is something I wrote to get error back from the CDO object. Here's what I get when I send a simple message with it. If anyone has any ideas, I would really appreciate some advice. This is putting a stop most of my mailing capabilities.

Thanks,

--Travis

**************************************
sp_OASetProperty, To
Source: NULL
Desc: Exception 0xc0000005 was generated at address 0x064bb021
**************************************
sp_OASetProperty, CC
Source: NULL
Desc: Exception 0xc0000005 was generated at address 0x064bb021
**************************************
sp_OASetProperty, From
Source: NULL
Desc: Exception 0xc0000005 was generated at address 0x064bb021
**************************************
sp_OASetProperty, Subject
Source: NULL
Desc: Exception 0xc0000005 was generated at address 0x064bb021
**************************************
sp_OASetProperty, HTMLBody
Source: NULL
Desc: Exception 0xc0000005 was generated at address 0x064bb021
**************************************
sp_OAMethod, Send
Source: NULL
Desc: Exception 0xc0000005 was generated at address 0x064bb021

Description: Exception 0xc0000005 was generated at address 0x064bb021

ALTER PROCEDURE [dbo].[sp_send_cdosysmail_html]
(
@From varchar(100) ,
@To varchar(500) ,
@Subject varchar(100)=" ",
@Body varchar(7000) =" ",
@CC varchar(500) = null
)
/******************************************
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
*******************************************/

/**
* History : 03.30.06 TLS - Added @CC field
**/

AS
Declare @iMsg int
Declare @hr int
Declare @source varchar(255)
Declare @description varchar(500)
Declare @output varchar(1000)

Declare @SMTP_IP_ADDRESS varchar(20) --5.10.05 TLS
BEGIN

SELECT @SMTP_IP_ADDRESS = '192.168.0.2'

--***** Create the CDO.Message Object *****

EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT
SELECT @output = dbo.handleOAError(@hr, 'sp_OACreate')
if(@output != '')
print @output

-- 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

("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
SELECT @output = dbo.handleOAError(@hr, 'sp_OASetProperty, sendusing')
if(@output != '')
print @output

-- 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

("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', @SMTP_IP_ADDRESS
SELECT @output = dbo.handleOAError(@hr, 'sp_OASetProperty, smptserver')
if(@output != '')
print @output

-- Save the configurations to the message object.
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null
SELECT @output = dbo.handleOAError(@hr, 'sp_OASetProperty, update')
if(@output != '')
print @output

-- Set the e-mail parameters.
EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
SELECT @output = dbo.handleOAError(@hr, 'sp_OASetProperty, To')
if(@output != '')
print @output

IF(@CC IS NOT NULL)
BEGIN
EXEC @hr = sp_OASetProperty @iMsg, 'CC', @CC
SELECT @output = dbo.handleOAError(@hr, 'sp_OASetProperty, CC')
if(@output != '')
print @output
END

EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
SELECT @output = dbo.handleOAError(@hr, 'sp_OASetProperty, From')
if(@output != '')
print @output

EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject
SELECT @output = dbo.handleOAError(@hr, 'sp_OASetProperty, Subject')
if(@output != '')
print @output

-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @hr = sp_OASetProperty @iMsg, 'HTMLBody', @Body
SELECT @output = dbo.handleOAError(@hr, 'sp_OASetProperty, HTMLBody')
if(@output != '')
print @output

EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL
SELECT @output = dbo.handleOAError(@hr, 'sp_OAMethod, Send')
if(@output != '')
print @output

-- Sample error handling.

IF @hr <>0
BEGIN
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
END
EXEC @hr = sp_OADestroy @iMsg
RETURN 1
END

-- cleanup
EXEC @hr = sp_OADestroy @iMsg

-- handle cleanup error
SET @output = dbo.handleOAError(@hr, 'sp_OADestroy')
if @output != ''
begin
print @output
return 1
end
RETURN 0
END
GO

domus71
Starting Member

2 Posts

Posted - 2006-04-19 : 05:36:17

I have the same problem with CDO.Message object.
I've got the same error message afte this line:

EXEC @hr = sp_OASetProperty @iMsg, 'To', @To

with error message:
Source: NULL
Desc: Exception 0xc0000005 was generated at address 0x064bb021

Any suggestions?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources