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
