sp_OAMethod usage (Problem in Stored procedure)

From: sp_OAMethod usage (_at_discussions.microsoft.com)
Date: 11/01/04 

Date: Sun, 31 Oct 2004 21:48:05 -0800

Hi

   I have SQL Server 2000 with Service Pack 3 in the Production Server. We
need to send mail using stored procedure. We dont have any IIS, SMTP and
Mail client software(Like Outlook, Exchange Server) in our Machine (this is
our requirement). That means we have to use remote SMTP server. So we
planned to use CDOSYS. Our code is like

CREATE procedure sp_send_cdosysmail1
@From varchar(100) ,
@To varchar(100) ,
@Subject varchar(100)=" ",
@Body varchar(4000) =" "

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

EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT

EXEC @hr = sp_OASetProperty @iMsg,
'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
EXEC @hr = sp_OASetProperty @iMsg,
'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', 'mailserverip'
EXEC @hr = sp_OASetProperty @iMsg,
'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout").Value', '10'
EXEC @hr = sp_OASetProperty @iMsg,
'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserverport").Value', '25'

EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null

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

EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body
EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL

IF @hr <>0
select @hr
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

EXEC @hr = sp_OADestroy @iMsg
GO

Note: In the "mailserverip" i have given the ip address of my smpt server.

This code is successfully executing in Development server. But not
executing the Production Server. We are facing the following error in the
Production Server while running the procedure.

Source: ODSOLE Extended Procedure
Description: sp_OAMethod usage: ObjPointer int IN, MethodName varchar IN [,
@returnval <any> OUT [, additional IN, OUT, or BOTH params]]

  Any Help would be appreciated for the guideline for useful suggestions

Regards
Arunachalam. S

Relevant Pages