September 22, 2008 at 7:34 pm
Hi All
over the years there have been a number of posts regarding the above.
I am in a position where I am in need of a specific answer to an age old question, but first let me give you some background.
I have a SQL Server server with a named instance of SQL Server 2000 and another named instance of SQL Server 2005.
Both these installations are serving as a L1 DW SQL Server staging platform.
I am doing transaction log restores to a mixture of SQL 2k and 2k5 databases.
I need to email a failure log when the job fails.
I am choosing to use send_cdosysmail instead of sql mail and database mail as I want only one way of doing things so its easier to support.
I have in the past used the send_cdosysmail procedure without problems, but never needed to attach a log file.
So this is what I have:
a sql agent job that
1) kills database connections
2) restores/applies the transaction logs to the database
3) copies across a marker file for another process
4) the failure send mail routine.
Steps 1-3 have a on failure goto step 4. On success quit.
All steps append to a logfile except the first step.
I have a single store procedure send_cdosysmail code below. I can use this from a query window but when called from a step in the sql agent job does not send me an email with the attachment - just the email.
I have spent the last day trying all sorts of resolutions to no avail -
When I execute this stored proc from the query window I do get an error message :-
'Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.' - but choose to ignore as I get the email anyway.
However from the sql agent job I do not get the attachment - just the email.
Can anyone tell me what I am doing wrong and I promise to post back the results that work.
thanks
------------------send_cdosysmail code --------------------------------
USE [DBA_Admin]
GO
/****** Object: StoredProcedure [dbo].[send_cdosysmail] Script Date: 09/23/2008 10:49:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[dba_send_cdosysmail]
@From VARCHAR(100) = NULL,
@To VARCHAR(100),
@Subject VARCHAR(100) = " ",
@Body VARCHAR(4000) = " ",
@Attachments varchar(8000) = null
/*********************************************************************
This stored procedure takes the above parameters and sends an e-mail.
All of the mail configurations are hard-coded in the stored procedure.
Comments are added to the stored procedure where necessary.
Reference 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
set nocount on
DECLARE @iMsg INT
DECLARE @hr INT
Declare @int int
DECLARE @source VARCHAR(255)
DECLARE @description VARCHAR(500)
DECLARE @output VARCHAR(1000)
Declare @Tmp varchar(8000)
DECLARE @rv int
IF @from = NULL
BEGIN
SELECT @from = @@servername
END
--************* Create the CDO.Message Object ************************
EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT
--***************Configuring the Message Object ******************
-- This is to configure a remote SMTP server.
EXEC @hr = sp_OASetProperty @iMsg,
'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value',
'2'
-- 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',
'mailhost'
-- Save the configurations to the message object.
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', NULL
-- Set the e-mail parameters.
EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject
-- adding an attachment:
if len(@Attachments)>0
begin
while len(@Attachments)>0
begin
set @int = charindex(';',@Attachments)
if @int > 0
begin
set @Tmp = left(@Attachments,@int-1)
set @Attachments = right(@Attachments,(len(@Attachments)-@int))
end else
begin
set @Tmp = @Attachments
set @Attachments = ''
end /* Add the Attachment */
Print @Tmp
EXEC @hr= sp_OAMethod @iMsg, 'AddAttachment', NULL, @Tmp
end
end
-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body
EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL
-- Sample error handling.
IF @hr <> 0
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
October 2, 2008 at 9:05 am
I get this same error when trying to add an attachment to my CDO proc. I still receive the email and the attachment however it still throws this error.
If I find anything I'll post it.
October 2, 2008 at 9:26 am
John,
the only difference between running sproc from Query Analyzer or from Job is a security context.
In query analyzer you are running your script under the current connection security context.
When running from job step you are running under security context of Agent account.
If your agent does not have security rights on the file system where your log is located, attachment will not be delivered, while email will be send...
October 2, 2008 at 9:36 am
The issue is in this statement:
exec @hr = sp_OAMethod @object, 'AddAttachment',NULL, @Tmp
You can't provide a NULL value for that output parameter. You must provide a blank value or declare a variable and use it there:
exec @hr = sp_OAMethod @object, 'AddAttachment','', @Tmp
declare @OutPutParam int
exec @hr = sp_OAMethod @object, 'AddAttachment',@OutPutParam, @Tmp
If you use the extended proc sp_displayoaerrorinfo it'll display error info for sp_OAMethod etc...
October 2, 2008 at 12:00 pm
I'm pretty sure I did try the declared variable - however its been a week now and I have had to move on. I will still try this out again - probably next week, but in the mean time, my error message has a hardcoded HTML link to the error log file.
This way the reciepient does not have to log onto the server or navigate to the directory - just click on the log file and it should open up
If I get anywhere I'll post what I have learn't here next week
thanks all
September 14, 2009 at 5:21 am
Hi,
When I tried to use the variable as suggested, the proc just stops to send any mails, though there were no errors this time. Please suggest
declare @OutPutParam int
IF ISNULL(@FilePath,'')''
print @FilePath
BEGIN
EXEC sp_OAMethod @newmail, 'AddAttachment',@OutPutParam,@FilePath
END
September 14, 2009 at 6:27 am
You need to capture the OA error output of the various methods being used. You may have an OA error but the email procedure won't display that info without the proc sp_displayoaerrorinfo being used within it.
For example: I can provide an invalid attachment path to my CDOSend proc and it'll report a successful execution but because I've utilized the sp_DisplayOAErrorInfo proc it'll tell me that the path provided is invalid. If I didn't use the sp_DispalyOAErrrorInfo my proc will not return any "errors". Error handling needs to be built within your procedures too.
September 14, 2009 at 7:35 am
Many thanks for the reply.. I managed to get the attachment working.When I execute my SP it sent the email out but at the end it shows the below error
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
I found few post to overcome this issue by declaring a variable instead of passing NULL directly, in the below statement I added @OutPutParam as int and set is to null and later replaced it will NULL in the floowing statement. Here, the code works without any error and mail came out without attachment.
PLEASE HELP
declare @OutPutParam int
set @OutPutParam = NULL
IF ISNULL(@FilePath,'')''
BEGIN
print 'inside'
EXEC sp_OAMethod @newmail, 'AddAttachment',@OutPutParam,@FilePath
END
September 14, 2009 at 8:44 am
IN addition to this..I have a server with win 2003 and there I have a Sp which uses CDONTS to send mail...I have configured everything there but email is not going out when I try to run the SP it says:
Error sending message
OLE Automation Error Information
HRESULT: 0x80070005
CDONT.dll is present and registered. same SP is working on one of my testing server with same configuration...
September 14, 2009 at 8:55 am
If you use the sp_DisplayOAErrorInfo which calls sp_hexidecmal and sp_OAGetErrorInfo (Extended System SP) it'll convert that HResult into a readable error.
I'm pretty sure you'll have to create sp_DisplayOAErrorInfo and sp_HexiDecimal system procedures. The documentation can be found in the BOL under "OLE Automation Return Codes and Error Information"
Any paticular reason your using CDONTS instead of CDOSYS?
CDONTS was replaced by CDOSYS in W2K and is better to use according to MS: http://support.microsoft.com/kb/315197
September 14, 2009 at 9:04 am
I have to migrate a application which is currently on win 2000 server to win 2003 server, I agree with you that CDONTS.dll has been discontinued, but howcome it is working on one of my test server.
Additionally, I have included the exception catch and got the below description..
Error sending message
OLE Automation Error Information
HRESULT: 0x80042732
Source: ODSOLE Extended Procedure
Description: Output values of type Object are not allowed in result sets.
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
September 14, 2009 at 9:08 am
Below is the complete code of the proc using CDONTS..
set ANSI_NULLS OFF
set QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE SendEmail
(
@From varchar(255)
, @To varchar(8000)
, @Subject varchar(255)
, @Bodyvarchar(8000)
, @cc varchar(8000) = NULL
, @Bcc varchar(8000) = NULL
, @FilePathvarchar(200) = NULL
)
AS
DECLARE @newmail int
DECLARE @result int
-- Create the CDONTS.Newmail object
EXEC @result = sp_OACreate 'CDONTS.NewMail', @newmail OUT
IF @result 0
BEGIN
PRINT 'Error creating object'
EXEC TEMS.sp_displayoaerrorinfo @newmail, @result
RETURN
END
-- Check the Optional Properties
IF not @cc = NULL
BEGIN
EXEC @result = sp_OASetProperty @newmail, 'cc', @cc
IF @result 0
BEGIN
PRINT 'Error setting [cc] property'
EXEC TEMS.sp_displayoaerrorinfo @newmail, @result
RETURN
END
END
IF not @Bcc = NULL
BEGIN
EXEC @result = sp_OASetProperty @newmail, 'Bcc', @Bcc
IF @result 0
BEGIN
PRINT 'Error setting [Bcc] property'
EXEC TEMS.sp_displayoaerrorinfo @newmail, @result
RETURN
END
END
EXEC @result = sp_OASetProperty @newmail, 'BodyFormat',0
IF @result 0
BEGIN
PRINT 'Error setting BodyFormat property'
EXEC TEMS.sp_displayoaerrorinfo @newmail, @result
RETURN
END
EXEC @result = sp_OASetProperty @newmail, 'MailFormat',0
IF @result 0
BEGIN
PRINT 'Error setting MailFormat property'
EXEC TEMS.sp_displayoaerrorinfo @newmail, @result
RETURN
END
--Set the non-optional properties
EXEC @result = sp_OASetProperty @newmail, 'From', @From
EXEC @result = sp_OASetProperty @newmail, 'To', @To
EXEC @result = sp_OASetProperty @newmail, 'Subject', @Subject
EXEC @result = sp_OASetProperty @newmail, 'Body', @Body
IF ISNULL(@FilePath,'')''
BEGIN
EXEC @result = sp_OAMethod @newmail, 'AttachFile', NULL, @FilePath
IF @result 0
BEGIN
PRINT 'Error attacting message'
EXEC TEMS.sp_displayoaerrorinfo @newmail, @result
RETURN
END
END
-- Send the message...
EXEC @result = sp_OAMethod @newmail,'Send'
IF @result 0
BEGIN
PRINT 'Error sending message'
EXEC TEMS.sp_displayoaerrorinfo @newmail, @result
RETURN
END
-- Destroy the object.
EXEC @result = sp_OADestroy @newmail
IF @result 0
BEGIN
PRINT'Error destroying object'
EXEC TEMS.sp_displayoaerrorinfo @newmail, @result
RETURN
END
September 14, 2009 at 9:10 am
this is using CDO
set ANSI_NULLS ON
set QUOTED_IDENTIFIER OFF
SET NOCOUNT ON
GO
alter PROCEDURE [dbo].[Test_SendMail_1]
@From varchar(255) ,
@To varchar(255) ,
@Subject varchar(255),
@Body varchar(8000),
@cc varchar(255) = NULL,
@Bcc varchar(8000) = NULL,
@FilePathvarchar(4000) = NULL
AS
Declare @newmail int
Declare @result int
Declare @source varchar(255)
Declare @description varchar(500)
Declare @output varchar(1000)
--************* Create the CDO.Message Object ************************
EXEC @result = sp_OACreate 'CDO.Message', @newmail OUT
IF @result 0
BEGIN
PRINT 'Error creating object'
EXEC sp_displayoaerrorinfo @newmail, @result
RETURN
END
--***************Configuring the Message Object ******************
-- This is to configure a remote SMTP server.
EXEC @result = sp_OASetProperty @newmail,'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
IF @result 0
BEGIN
PRINT 'Error configuring remote SMTP server'
EXEC sp_displayoaerrorinfo @newmail, @result
RETURN
END
---- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @result = sp_OASetProperty @newmail,'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', 'SMTP Server IP'
IF @result 0
BEGIN
PRINT 'Error configuring IP of SMTP server'
EXEC sp_displayoaerrorinfo @newmail, @result
RETURN
END
--smtp server
-- Save the configurations to the message object.
EXEC @result = sp_OAMethod @newmail, 'Configuration.Fields.Update', null
IF @result 0
BEGIN
PRINT 'Error while saving configuration to the message object'
EXEC sp_displayoaerrorinfo @newmail, @result
RETURN
END
-- Set the e-mail parameters.
EXEC @result = sp_OASetProperty @newmail, 'To', @To
EXEC @result = sp_OASetProperty @newmail, 'From', @From
IF not @cc = NULL
print @cc
BEGIN
EXEC @result = sp_OASetProperty @newmail, 'CC', @cc
IF @result 0
BEGIN
PRINT 'Error setting [cc] property'
EXEC sp_displayoaerrorinfo @newmail, @result
RETURN
END
END
print @Bcc
IF not @Bcc = NULL
BEGIN
EXEC @result = sp_OASetProperty @newmail, 'BCC', @Bcc
IF @result 0
BEGIN
PRINT 'Error setting [Bcc] property'
EXEC sp_displayoaerrorinfo @newmail, @result
RETURN
END
END
EXEC @result = sp_OASetProperty @newmail, 'HTMLBody', @Body
EXEC @result = sp_OASetProperty @newmail, 'Subject', @Subject
declare @OutPutParam int
set @OutPutParam = NULL
IF ISNULL(@FilePath,'')''
BEGIN
print 'inside'
EXEC @result = sp_OAMethod @newmail, 'AddAttachment',NULL,@FilePath
IF @result 0
BEGIN
PRINT 'Error sending message'
EXEC sp_displayoaerrorinfo @newmail, @result
RETURN
END
END
EXEC @result = sp_OAMethod @newmail, 'Send', NULL
IF @result 0
BEGIN
PRINT 'Error sending message'
EXEC sp_displayoaerrorinfo @newmail, @result
RETURN
END
-- Destroy the object.
EXEC @result = sp_OADestroy @newmail
select @newmail
print 'inside Destroy'
IF @result 0
BEGIN
PRINT'Error destroying object'
EXEC sp_displayoaerrorinfo @newmail, @result
RETURN
END
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply