April 18, 2012 at 11:21 am
I have 2008R 2, send mail is enaled, operators is configured, but somehow I am not receiving the amil
April 18, 2012 at 11:26 am
Are you sending one? 🙂 What are you doing to send the emails. Please provide the script in your post.
Jared
CE - Microsoft
April 18, 2012 at 11:28 am
Alert of the jobs
April 18, 2012 at 11:30 am
USE [msdb]
GO
/****** Object: StoredProcedure [dbo].[xp_sendmail] Script Date: 04/18/2012 13:21:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[xp_sendmail]
@recipientsvarchar(max)=NULL,
@messagevarchar(max)=NULL,
@queryvarchar(max)=NULL,
@attachmentsvarchar(max)=NULL,
@copy_recipients varchar(max)=NULL,
@blind_copy_recipientsvarchar(max)=NULL,
@subjectvarchar(max)=NULL,
@typesysname=NULL,
@attach_resultschar(5)=NULL,
@no_outputchar(5)=NULL,
@no_headerchar(5)=NULL,
@widthint=9,
@separatorchar(1)='',
@echo_errorchar(5)=NULL,
@set_usersysname=NULL,
@dbusesysname=NULL,
--call parameters outside the range of old xp_sendmail call
@profile_namesysname=NULL,
@body_formatVARCHAR(20)='TEXT',
@importanceVARCHAR(6)='NORMAL',
@sensitivityVARCHAR(12)='NORMAL',
@query_attachment_filenameNVARCHAR(260)=NULL,
@append_query_errorBIT=0,
@query_no_truncateBIT=0,
@query_result_no_paddingBIT=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_headerbit,
@exclude_query_outputbit,
@rcint =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
April 18, 2012 at 11:32 am
Hmm... Still not a mindreader. Are you setting an alert or a notification? Can you send an email using db_sendmail? Is the job "supposed" to send an email (alert or notification?) upon success, failure, both?
If you were going to describe this issue to your boss, would you just say "email is not working?" I hope not. Please give us the details of the issue starting with exactly how you configured email, if you are getting any errors, and why exactly you think you should be receiving an email.
Jared
CE - Microsoft
April 18, 2012 at 11:34 am
Sorry is Alert for the job failure
April 18, 2012 at 11:43 am
dsohal (4/18/2012)
USE [msdb]GO
/****** Object: StoredProcedure [dbo].[xp_sendmail] Script Date: 04/18/2012 13:21:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[xp_sendmail]
@recipientsvarchar(max)=NULL,
@messagevarchar(max)=NULL,
@queryvarchar(max)=NULL,
@attachmentsvarchar(max)=NULL,
@copy_recipients varchar(max)=NULL,
@blind_copy_recipientsvarchar(max)=NULL,
@subjectvarchar(max)=NULL,
@typesysname=NULL,
@attach_resultschar(5)=NULL,
@no_outputchar(5)=NULL,
@no_headerchar(5)=NULL,
@widthint=9,
@separatorchar(1)='',
@echo_errorchar(5)=NULL,
@set_usersysname=NULL,
@dbusesysname=NULL,
--call parameters outside the range of old xp_sendmail call
@profile_namesysname=NULL,
@body_formatVARCHAR(20)='TEXT',
@importanceVARCHAR(6)='NORMAL',
@sensitivityVARCHAR(12)='NORMAL',
@query_attachment_filenameNVARCHAR(260)=NULL,
@append_query_errorBIT=0,
@query_no_truncateBIT=0,
@query_result_no_paddingBIT=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_headerbit,
@exclude_query_outputbit,
@rcint =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
What is this?
Jared
CE - Microsoft
April 18, 2012 at 11:47 am
sorry, I am going to notification tab of job and select the email address
April 18, 2012 at 11:52 am
I am still not receiving the notification after the job fails
April 18, 2012 at 11:57 am
Have you setup database mail for SQL Server Agent? IIRC, this is a separate step from just setting up database mail. Sorry if I'm not sure, but it has been several years since I had to do this.
April 18, 2012 at 1:08 pm
Lynn Pettis (4/18/2012)
Have you setup database mail for SQL Server Agent? IIRC, this is a separate step from just setting up database mail. Sorry if I'm not sure, but it has been several years since I had to do this.
after you configure DB MAIL you have to enable it in sql server agent (right click on sql server agent in object explorer then go to alert system) after you have enabled the alerts and set up every thing you then need to restart the SQL server agent service before it will start sending mail. that was fun to figure out why my agent was not sending an email for my test fail job (job step of "SELECT COUNT(*), col1 from table1" specifficly to throw an error). after more googleing realized i needed to restart the agent service and every thing started working.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 18, 2012 at 1:19 pm
capn.hector (4/18/2012)
Lynn Pettis (4/18/2012)
Have you setup database mail for SQL Server Agent? IIRC, this is a separate step from just setting up database mail. Sorry if I'm not sure, but it has been several years since I had to do this.after you configure DB MAIL you have to enable it in sql server agent (right click on sql server agent in object explorer then go to alert system) after you have enabled the alerts and set up every thing you then need to restart the SQL server agent service before it will start sending mail. that was fun to figure out why my agent was not sending an email for my test fail job (job step of "SELECT COUNT(*), col1 from table1" specifficly to throw an error). after more googleing realized i needed to restart the agent service and every thing started working.
Thank you capn, I hope this helps the OP.
April 19, 2012 at 9:34 am
Everything looked fine to me, I had to restart the SQL Agent Job, and that make the notification working,
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply