November 20, 2012 at 8:34 am
I have everything configured, have the sendemail stored procedure under msdb, havethe operators,
same configuration on other 2 servers work with no issue,
Help is appreciated,
Bubby
November 20, 2012 at 8:38 am
Have you restarted the Agent service since you told it which mail profile to use under the Alerts system properties?
November 20, 2012 at 8:42 am
I have restarted the agent, I am using notification for job faliure
November 20, 2012 at 8:45 am
Can you send a test mail successfully via sp_send_dbmail?
November 20, 2012 at 8:49 am
can I please get a script
November 20, 2012 at 8:51 am
EXEC msdb.dbo.sp_send_dbmail
@profile_name = '',
@recipients = '',
@body = '',
@subject = '' ;
November 20, 2012 at 8:56 am
I am using this,
USE [msdb]
GO
/****** Object: StoredProcedure [dbo].[xp_sendmail] Script Date: 11/20/2012 10:27:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROC [dbo].[xp_sendmail]
@recipients varchar(max) = NULL,
@message varchar(max) = NULL,
@query varchar(max) = NULL,
@attachments varchar(max) = NULL,
@copy_recipients varchar(max) = NULL,
@blind_copy_recipients varchar(max) = NULL,
@subject varchar(max) = NULL,
@type sysname = NULL,
@attach_results char(5) = NULL,
@no_output char(5) = NULL,
@no_header char(5) = NULL,
@width int = 9,
@separator char(1) = '',
@echo_error char(5) = NULL,
@set_user sysname = NULL,
@dbuse sysname = NULL,
--call parameters outside the range of old xp_sendmail call
@profile_name sysname = NULL,
@body_format VARCHAR(20) = 'TEXT',
@importance VARCHAR(6) = 'NORMAL',
@sensitivity VARCHAR(12) = 'NORMAL',
@query_attachment_filename NVARCHAR(260) = NULL,
@append_query_error BIT = 0,
@query_no_truncate BIT = 0,
@query_result_no_padding BIT = 0
AS
/**************************************************************************
-- xp_sendmail.sql --
DESCRIPTION: Subsitute call of xp_sendmail
with a call to sp_send_dbmail
Uses all normal xp_sendmail params mapped to sp_send_dbmail
***************************************************************************/
BEGIN
SET NOCOUNT ON
-- And make sure ARITHABORT is on.
SET ARITHABORT ON
-- Local mappings of distant vars.
DECLARE @attach_query_result_as_file bit,
@query_result_header bit,
@exclude_query_output bit,
@rc int =0
-- Cannot be NULL, but TRUE as 1 or FALSE as 0
IF (@attach_results) IS NOT NULL
BEGIN
IF (@attach_results = 'TRUE')
set @attach_query_result_as_file = 1;
END
ELSE set @attach_query_result_as_file = 0;
-- Cannot be NULL, but TRUE as 1 or FALSE as 0
IF (@no_header) IS NOT NULL
BEGIN
IF (@no_header = 'TRUE')
set @query_result_header = 1;
END
ELSE set @query_result_header = 0;
-- Cannot be NULL, but TRUE as 1 or FALSE as 0
IF (@echo_error) IS NOT NULL
BEGIN
IF (@echo_error = 'TRUE')
set @exclude_query_output = 1;
END
ELSE set @exclude_query_output = 0;
-- SQLCMD requires width between 9 and 65536 - 1
IF (@width) < 9
set @width = 9;
ELSE IF (@width) > 65535
set @width = 65535;
-- Call to 2008 version, all params strong type-cast
EXEC @rc = msdb.dbo.sp_send_dbmail
@profile_name,
@recipients,
@copy_recipients,
@blind_copy_recipients,
@subject,
@message,
@body_format,
@importance,
@sensitivity,
@attachments,
@query,
@dbuse,
@attach_query_result_as_file,
@query_attachment_filename,
@query_result_header,
@width,
@separator,
@exclude_query_output,
@append_query_error,
@query_no_truncate,
@query_result_no_padding
;
RETURN (@RC)
END
November 20, 2012 at 8:57 am
Well if you use that procedure and pass in the parameters do you get an email from it?
November 20, 2012 at 9:03 am
No i do not, i get in other servers, is ther any script I can dreaft it of this procedure to test
November 20, 2012 at 11:34 am
Have you enabled and configured database mail?
November 20, 2012 at 11:36 am
as mentioned I aqm not using data mail
November 20, 2012 at 12:36 pm
Yes you are, your xp_sendmail maps to sp_send_dbmail so you need to ensure you have database mail enabled and configured
November 22, 2012 at 9:37 am
I can get a test email from Database Mail no problem, bit not the job faliure
November 23, 2012 at 2:19 am
And how are you doing the notification? A job step which is triggered should a step fail? Using a notification to an operator on job failure?
If notification of an operator then you need to ensure you have set SQL Agent to use DB Mail, given it a profile, ensure the operators exist, restart SQL Agent.
If job step on failure then you will need to diagnose what is going on in that jobstep.
November 23, 2012 at 9:29 am
take a look at the mail tables
i use these queries
select * from msdb..sysmail_event_log order by log_date desc;
-- all mail sent
select top 100 * from msdb..sysmail_allitems order by sent_date desc;
-- all failed mail
select top 100 * from msdb..sysmail_faileditems order by sent_date desc;
-- all unsent mail
select * from msdb..sysmail_unsentitems order by sent_date desc;
select * from msdb..sysmail_send_retries order by 4 desc
-- Check mail queue
use msdb
go
exec msdb..sysmail_help_queue_sp @queue_type = 'Mail' ;
exec msdb..sysmail_help_status_sp;
--EXECUTE msdb.dbo.sysmail_stop_sp;
--EXECUTE msdb.dbo.sysmail_start_sp;
I usually start with the failed mail first - it might give you a good reason why, it's possible the mail profile the procedure uses has a bad smtp server or something as silly as that.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply