September 15, 2005 at 12:31 pm
I apologize for the long code sample, but this was originally cribbed from http://support.microsoft.com/default.aspx?scid=kb;en-us;312839 and then I modified it to add cc, bcc, and attachments.
This has worked since August 25 when called by an Execute SQL Task in numerous DTS packages. It now works only intermittently, on one server but not another. As far as we know, nothing in the environment has changed; no service packs, no updates of any kind. It does not return error messages, the sp_OA* procs return zero (success) codes, but no email gets sent.
The MSFT link "strongly" recommends that we use an SMTP pickup directory instead of sending to the SMTP server. Does anyone have a sample of that technique to share?
Also, the link shows setting configurations this way:
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
Does this reach out to the microsoft website?
Thanks.
----------------------------------------------------------------------------
USE msdb
GO
CREATE PROCEDURE dbo.sp_send_cdosysmail
@From varchar(100) = '',-- Aug 23, 2005 by Dave ; made optional, default to dbanotify@integres.com
@To varchar(100) ,-- Aug 23, 2005 by Dave ; separate multiple emails with semicolon
@cc varchar(100) = '',-- Aug 23, 2005 by Dave ; separate multiple emails with semicolon
@bcc varchar(100) = '',-- Aug 23, 2005 by Dave ; separate multiple emails with semicolon
@Subject varchar(100) = '',-- Aug 23, 2005 by Dave ; made optional, default to "Message from sp_send_cdosysmail on " + servername
@Body varchar(4000) = ' ',
@Attachment varchar(400) = ''-- Aug 23, 2005 by Dave ; full path to file to be attached
/*********************************************************************
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
-- Aug 23, 2005 by Dave
1. Added ability to specify a file to be sent as an attachment. Use full path to file
"c:\mssql\blah\yourfile.txt" or UNC path.
2. Added cc and bcc parameters.
3. Added default for From and Subject parameters.
***********************************************************************/
AS
SET NOCOUNT ON
Declare @iMsg int
Declare @hr int
Declare @source varchar(255)
Declare @description varchar(500)
Declare @output varchar(1000)
Declare @Ret int-- the return value parameter required by sp_OACreate when calling it with 4 parameters
--************* Create the CDO.Message Object ************************
EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT
IF @hr <>0
BEGIN
SELECT @hr
INSERT INTO dbo.cdosysmail_failures VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OACreate')
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
INSERT INTO dbo.cdosysmail_failures VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OACreate')
RETURN
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
--***************Configuring the Message Object ******************
-- This is to configure a remote SMTP server.
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("sendusing").Value','3'-- use the pickup directory
IF @hr <>0
BEGIN
-- SELECT @hr
INSERT INTO dbo.cdosysmail_failures VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OASetProperty sendusing')
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
INSERT INTO dbo.cdosysmail_failures VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty sendusing')
GOTO send_cdosysmail_cleanup
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
GOTO send_cdosysmail_cleanup
END
END
-- 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("smtpserver").Value', 'ip address here'
IF @hr <>0
BEGIN
SELECT @hr
INSERT INTO dbo.cdosysmail_failures VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OASetProperty smtpserver')
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
INSERT INTO dbo.cdosysmail_failures VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty smtpserver')
GOTO send_cdosysmail_cleanup
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
GOTO send_cdosysmail_cleanup
END
END
-- Save the configurations to the message object.
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null
IF @hr <>0
BEGIN
SELECT @hr
INSERT INTO dbo.cdosysmail_failures VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OASetProperty Update')
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
INSERT INTO dbo.cdosysmail_failures VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty Update')
GOTO send_cdosysmail_cleanup
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
GOTO send_cdosysmail_cleanup
END
END
-- Set the e-mail parameters.
EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
IF @hr <>0
BEGIN
SELECT @hr
INSERT INTO dbo.cdosysmail_failures VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OASetProperty To')
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
INSERT INTO dbo.cdosysmail_failures VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty To')
GOTO send_cdosysmail_cleanup
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
GOTO send_cdosysmail_cleanup
END
END
IF (LEN(@From) = 0)BEGIN
SET @From = 'dbanotify@integres.com'
END
EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
IF @hr <>0
BEGIN
SELECT @hr
INSERT INTO dbo.cdosysmail_failures VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OASetProperty From')
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
INSERT INTO dbo.cdosysmail_failures VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty From')
GOTO send_cdosysmail_cleanup
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
GOTO send_cdosysmail_cleanup
END
END
IF (LEN(@Subject) = 0)
BEGIN
SET @Subject = 'Message from sp_send_cdosysmail on ' + CAST(SERVERPROPERTY('MachineName') as varchar(24))
END
EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject
IF @hr <>0
BEGIN
SELECT @hr
INSERT INTO dbo.cdosysmail_failures VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OASetProperty Subject')
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
INSERT INTO dbo.cdosysmail_failures VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty Subject')
GOTO send_cdosysmail_cleanup
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
GOTO send_cdosysmail_cleanup
END
END
-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body
IF @hr <>0
BEGIN
SELECT @hr
INSERT INTO dbo.cdosysmail_failures VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OASetProperty TextBody')
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
INSERT INTO dbo.cdosysmail_failures VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty TextBody')
GOTO send_cdosysmail_cleanup
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
GOTO send_cdosysmail_cleanup
END
END
-- Aug 23, 2005 by Dave
IF (LEN(@Attachment) > 0)
BEGIN
EXEC @hr = sp_OAMethod @iMsg, 'AddAttachment', @Ret OUTPUT, @Attachment
IF (@hr <> 0)
BEGIN
SELECT @hr
INSERT INTO dbo.cdosysmail_failures VALUES (getdate(), @@spid, @From, @To, @Subject, @Attachment, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OAMethod AddAttachment')
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
INSERT INTO dbo.cdosysmail_failures VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OAMethod AddAttachment')
GOTO send_cdosysmail_cleanup
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
GOTO send_cdosysmail_cleanup
END
END
END
-- Aug 23, 2005 by Dave
IF (LEN(@cc) > 0)
BEGIN
EXEC @hr = sp_OASetProperty @iMsg, 'CC', @cc
IF (@hr <> 0)
BEGIN
SELECT @hr
INSERT INTO dbo.cdosysmail_failures VALUES (getdate(), @@spid, @From, @To, @Subject, @cc, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OASetProperty CC')
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
INSERT INTO dbo.cdosysmail_failures VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty CC')
GOTO send_cdosysmail_cleanup
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
GOTO send_cdosysmail_cleanup
END
END
END
IF (LEN(@bcc) > 0)
BEGIN
EXEC @hr = sp_OASetProperty @iMsg, 'BCC', @BCC
IF (@hr <> 0)
BEGIN
SELECT @hr
INSERT INTO dbo.cdosysmail_failures VALUES (getdate(), @@spid, @From, @To, @Subject, @cc, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OASetProperty BCC')
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
INSERT INTO dbo.cdosysmail_failures VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty BCC')
GOTO send_cdosysmail_cleanup
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
GOTO send_cdosysmail_cleanup
END
END
END
EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL
IF @hr <>0
BEGIN
SELECT @hr
INSERT INTO dbo.cdosysmail_failures VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OAMethod Send')
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
INSERT INTO dbo.cdosysmail_failures VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OAMethod Send')
GOTO send_cdosysmail_cleanup
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
GOTO send_cdosysmail_cleanup
END
END
-- Do some error handling after each step if you have to.
-- Clean up the objects created.
send_cdosysmail_cleanup:
If (@iMsg IS NOT NULL) -- if @iMsg is NOT NULL then destroy it
BEGIN
EXEC @hr=sp_OADestroy @iMsg
-- handle the failure of the destroy if needed
IF @hr <>0
BEGIN
select @hr
INSERT INTO dbo.cdosysmail_failures VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OADestroy')
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
-- if sp_OAGetErrorInfo was successful, print errors
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
INSERT INTO dbo.cdosysmail_failures VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OADestroy')
END
-- else sp_OAGetErrorInfo failed
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
END
ELSE
BEGIN
PRINT ' sp_OADestroy skipped because @iMsg is NULL.'
INSERT INTO dbo.cdosysmail_failures VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, '@iMsg is NULL, sp_OADestroy skipped')
RETURN
END
September 16, 2005 at 11:30 am
Anyone? Anyone? Beuller?
September 16, 2005 at 12:52 pm
has anyone modified the settings on the SMTP server ? I know at our location we only allow specific IP addressess to 'forward' things toi the SMTP server. If you are using the security fesature then maybe it could explain your situation. Because I've been using that 'cobbled' proc for a few years now with almost 'zero' hiccups (except when I build a new server and do not update the SMTP security !
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
September 16, 2005 at 5:33 pm
Thanks, Rudy. We have set relaying on for authenticated servers, so I don't think that's it. Plus, sometimes it works, sometimes it doesn't. It returns a zero return code, it just doesn't send the email.
September 16, 2005 at 6:15 pm
Is there anything in the smtp logs to give you a clue?
*I had a devil of a time deugging a problem like this and it turned out that you couldn't use COM objects if "NT Fibers" was enabled. But that doesn't sound like your issue because your is intermittent.
I'd check the mail logs.
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors)
September 19, 2005 at 9:28 am
The plot thickens! Five of my test messages from 9-14 and 9-16 showed up early 9-18 with a "Undeliverable... Could not deliver the message in the time limit specified" message. Three were sent from a production system, two from a dev system. The ones I sent 9-15 are still MIA.
September 19, 2006 at 10:24 am
I was having this problem also. I traced it to port blocking of McAfee Virus Scan 8.0i Patch13. It was not allowing sqlserver.exe to send emails to my smtp server in order to prevent mailer worms.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply