SP for E-mail Failing
-
I have a sp that produces a CDO object to send mail because often the xp_sendmail sp in SQL will fail for me for various reasons (timeouts, etc.). This procedure was working fine until we recently change exchange servers (Exchange 2000 to Exchange 2003). I changed the IP inside the sp to the new server's IP and now it gives the error: The transport failed to connect to the server. I have tried using authentication and it doesn't make a difference. The weird thing is that I can write a normal vbscript file that uses a CDO.Message object, and it works fine on the same computer as the sp. Does anyone have any ideas why the sp is not working now, while the vbscripts are? Below is my code.....Thanks, Adam
CREATE PROCEDURE send_cdomail(@From varchar(100),
@to varchar(100),
@cc varchar(100),
@Subject varchar(100),
@Body varchar(500),
@Attachment varchar(150))
AS
declare @iMessage int,@Err int,@ErrOutput varchar(1000),@source varchar(300),@description varchar(500),@ErrBody varchar(1000)
EXEC @Err = sp_OACreate 'CDO.Message', @iMessage OUT
EXEC @Err = sp_OASetProperty @iMessage, 'Configuration(cdoSendUsingMethod)', 2
EXEC @Err = sp_OASetProperty @iMessage, 'Configuration(cdoSMTPServerName)', 'XXX.XX.XX.XXX'
EXEC @Err = sp_OASetProperty @iMessage, 'Configuration(cdoSMTPConnectionTimeout)', 30
EXEC @Err = sp_OAMethod @iMessage, 'Configuration.Fields.Update', null
EXEC @Err = sp_OASetProperty @iMessage, 'To', @To
EXEC @Err = sp_OASetProperty @iMessage, 'From', @From
EXEC @Err = sp_OASetProperty @iMessage, 'Subject', @Subject
EXEC @Err = sp_OASetProperty @iMessage, 'TextBody', @Body
EXEC @Err = sp_OAMethod @iMessage,'AddAttachment',null,@Attachment
EXEC @Err = sp_OAMethod @iMessage, 'Send', NULL
-- Error Handling
if @Err <> 0
begin
set @ErrBody = 'E-mail to ' + @To + ' (Subject: ' + @Subject + ') failed.'
exec master.dbo.xp_sendmail @Recipients='address@domain.com',@Subject='Test Subject',@Message=@ErrBody
exec @Err = sp_OAGetErrorInfo null,@source OUT,@description OUT
if @Err = 0
begin
select @ErrOutput = ' Source: ' + @source
print @ErrOutput
select @ErrOutput = ' Description: ' + @description
print @ErrOutput
end
else
begin
exec master.dbo.xp_sendmail @Recipients='address@domain.com',@Subject='Test Subject',@Message=@ErrBody
end
end
EXEC @Err = sp_OADestroy @iMessage
GO
-
Have you tried the sp_send_cdosysmail?
CREATE PROCEDURE dbo.sp_send_cdosysmail
@From varchar(100) ,
@To varchar(100) ,
@cc varchar(100),
@Subject varchar(100)=" ",
@Body varchar(4000) =" ",
@BodyType varchar(100) =" ", --HTMLBODY or TEXTBODY
@MailServerName varchar(100)
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("<A href="http://schemas.microsoft.com/cdo/configuration/sendusing"
.Value','2'">http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("<A href="http://schemas.microsoft.com/cdo/configuration/smtpserver"
.Value'">http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', @MailServerName EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null
EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
EXEC @hr = sp_OASetProperty @iMsg, 'CC', @cc
EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject
-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @hr = sp_OASetProperty @iMsg, @BodyType , @Body
EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL
-- Sample error handling.
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
-- Do some error handling after each step if you need to.
-- Clean up the objects created.
EXEC @hr = sp_OADestroy @iMsg
GO
-
Awesome, this worked......thanks a lot for the help!!!
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply