March 17, 2008 at 11:44 pm
I use SQL Server 2000 and use CDOSYS for sending mails through SQL Server. The SPs have been working fine and i was receiving mails till few months back. I suddenly realized that mails have stopped coming and that was when i started to troubleshoot the 'sp_send_cdosysmail' stored procedure. Since a list of parameters are configured and mail object is setup I set up markers to identify the step where the error was occuring. This is my version of 'cdo_sys_sendmail' with error identifications:
---------------------------------------------------------------------
Code Segment Start
---------------------------------------------------------------------
CREATE PROCEDURE [dbo].[sp_send_cdosysmail]
(
@From varchar(8000),
@To varchar(8000),
@Subject varchar(8000),
@Body varchar(8000),
@cc varchar(8000) = Null,
@Attachment varchar(500) =Null,
@Importance varchar(1) ='0' --Default importence LOW=0, NORMAL=1, HIGH = 2
)
/******************************************
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 @source varchar(255)
Declare @description varchar(500)
Declare @output varchar(1000)
--***** Create the CDO.Message Object *****
EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT
if @hr <> 0
print "message object creation failed"
--*****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'
if @hr <> 0
print "configuration failed"
-- 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',
'172.16.3.123'
if @hr <> 0
print "IP setting failed"
-- Save the configurations to the message object.
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null
if @hr <> 0
print "message object setting failed"
-- Set the e-mail parameters.
EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
if @hr <> 0
print "To failed"
EXEC @hr = sp_OASetProperty @iMsg, 'CC', @cc
if @hr <> 0
print "CC failed"
EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
if @hr <> 0
print "From failed"
EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject
if @hr <> 0
print "Subject failed"
--Attaching the attachment file
if @Attachment is not NULL
EXEC @hr = sp_OAMethod @iMsg,'AddAttachment',null,@Attachment
if @hr <> 0
print "Attcahment failed"
-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @hr = sp_OASetProperty @iMsg, 'HTMLBody', @Body
if @hr <> 0
print "Body failed"
-- Importance
EXEC @hr = sp_OASetProperty @iMsg,
'fields("urn:schemas:httpmail:importance").Value',@Importance
if @hr <> 0
print "Importance failed"
-- Save the configurations to the message object.
EXEC @hr = sp_OAMethod @iMsg, 'Fields.Update', null
if @hr <> 0
print "Fields.Update failed"
EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL
if @hr <> 0
print "Send failed"
--I commented out the default error handler so that i could use mine.
/*
-- 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 have to.
-- Clean up the objects created.
EXEC @hr = sp_OADestroy @iMsg
GO
---------------------------------------------------------------------
Code Segment Finish
---------------------------------------------------------------------
After calling the above SP i receive the "Send failed" error, which kind of highlights two things:
1. Either the existing configuration has gone bad.
2. 'Send' is the problem.
Kindly advice me on what could be the reason and how to rectify it issue.
March 19, 2008 at 4:01 am
Just a guess, but have you checked that the SMTP server still has the same address, is still working, and there is still a route through your local firewalls from the SQL to the SMTP box.
My experience is that Windows admin folk sometimes change these things without knowing what depends on the old settings.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply