Skip to main content

Email Alerts are not received...

In one of application, Email alerts are sent using SQL sp_send_dbmail stored procedure. Email alerts were working properly, but suddenly noticed email alerts are not received.

To troubleshoot the issue, first I checked the msdb.dbo.sysmail_event_log table using the below query.

SELECT log_id, event_type, log_date, description, process_id, mailitem_id, account_id, last_mod_date, last_mod_user
FROM
msdb.dbo.sysmail_event_log 
ORDER BY
log_date DESC

If there is any error, you should see records with the event_type value as error.
In my case there are records with event_type value as error, and the description value for those records is same as below

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 3 (2018-02-01T10:41:27). Exception Message: Cannot send mails to mail server. (The SMTP server requires a secure connection or the client was not authenticated. The server response was: Authentication required).  )

As per the error descript…

Stored Procedure to Populate Date Dimension Data

In this post I created a stored procedure to insert data to Date Dimension tables.
I have used four tables to have the Date structure. They are DimYear, DimQuarter, DimMonth and DimDay.

Script used to generate Dimension tables is given below.

CREATE TABLE dbo.DimYear
(
YearKey smallint IDENTITY(1,1) NOT NULL,
[Year] smallint NOT NULL,
CONSTRAINT PK_DimYear PRIMARY KEY (YearKey)

)
GO


CREATE TABLE dbo.DimQuarter
(
QuarterKey smallint IDENTITY(1,1) NOT NULL,
YearKey smallint NOT NULL,
[Quarter] smallint NOT NULL,
QuarterName varchar(20) NOT NULL,
CONSTRAINT PK_DimQuarter PRIMARY KEY (QuarterKey),
CONSTRAINT FK_DimQuarter_DimYear FOREIGN KEY (YearKey)
REFERENCES DimYear (YearKey)

)
GO


CREATE TABLE dbo.DimMonth
(
MonthKey smallint IDENTITY(1,1) NOT NULL,
QuarterKey smallint NOT NULL,
MonthNumber smallint NOT NULL,
[MonthName] varchar(20)NOT NULL,
[Month] varchar(20) NOT NULL,
CONSTRAINT PK_DimMonth PRIMARY KEY (MonthKey),
CONSTRAINT FK_DimMonth_DimQuarter FOREIGN KEY (QuarterKey)
REFERENCES DimQuarter (QuarterKey)

)
GO


CREATE TABLE dbo.DimDay
(
DayKey smallint IDENTITY(1,1) NOT NULL,
MonthKey smallint NOT NULL,
[Day] smallint NOT NULL,
[DayName] varchar(20) NOT NULL,
WeekNoForMonth smallint NOT NULL,
WeekNoForYear smallint NOT NULL,
CONSTRAINT PK_DimDay PRIMARY KEY (DayKey),
CONSTRAINT FK_DimDay_DimMonth FOREIGN KEY (MonthKey)
REFERENCES DimMonth (MonthKey)
)
GO



A Stored Procedure was used to fill those dimension tables. Below I put the script of the Stored Procedure



IF OBJECT_ID('dbo.PopulateDateDimension') IS NOT NULL
DROP PROC dbo.PopulateDateDimension
GO

CREATE PROCEDURE dbo.PopulateDateDimension @Year smallint
AS
BEGIN

DECLARE @StartMonth tinyint --set the current month value
DECLARE @EndMonth tinyint --set the last month for a quarter
DECLARE @YearKey smallint --YearKey value in the DimYear table
DECLARE @Quarter smallint --Quarter value
DECLARE @LastQuarter smallint --Last Quarter value and it is equal to 4
DECLARE @QuarterKey smallint --QuarterKey value in the DimQuarter table
DECLARE @StartDay tinyint --current prossesing date
DECLARE @EndDay tinyint --last date for the month
DECLARE @MonthKey smallint --MonthKey value in the DimMonth table
DECLARE @MonthName varchar --Month Name in the YYYY-Month format
DECLARE @WeekNoForMonth smallint --Value of the processing week for the month
DECLARE @WeekNoForYear smallint --Value of the processing week for the year
DECLARE @WeekDayName varchar(20) --Weekday string (Sunday, Monday,...)

SET @StartMonth = 1
SET @EndMonth = 3
SET @Quarter = 1
SET @LastQuarter=4
SET @StartDay=1
SET @EndDay=31


BEGIN TRY
BEGIN TRANSACTION

IF NOT EXISTS (SELECT * FROM dbo.DimYear WHERE [Year] = @Year) --check whether the processing year is already filled or not
BEGIN

SET @WeekNoForYear=1 -- Starts the week value for the year

INSERT INTO dbo.DimYear (Year) VALUES (@Year)
SELECT @YearKey = SCOPE_IDENTITY() --Get the last inserted YearKey


IF NOT EXISTS(SELECT * FROM dbo.DimQuarter WHERE [Quarter] = @Quarter AND YearKey = @YearKey) --Check whether the quarter value for the given year is exist or not
BEGIN
WHILE @Quarter<=@LastQuarter --check the all the quarter values are inserted or not
BEGIN
INSERT INTO dbo.DimQuarter
(YearKey, Quarter, QuarterName)
VALUES
(@YearKey,@Quarter, CONVERT(varchar(4), @Year) + ' - ' + 'Q'+CONVERT(varchar(4),@Quarter))
SELECT @QuarterKey = SCOPE_IDENTITY() --Get the last inserter QuarterKey



WHILE @StartMonth <= @EndMonth --check all the months are inserted or not
BEGIN



IF NOT EXISTS(SELECT * FROM dbo.DimMonth WHERE MonthNumber = @StartMonth AND QuarterKey = @QuarterKey) --check the processing Month record is inserted or not
INSERT INTO dbo.DimMonth
(QuarterKey,[Month], MonthName, MonthNumber)
VALUES
(@QuarterKey, DATENAME(mm, CONVERT(datetime, CONVERT(varchar(2), @StartMonth) + '/1/2000', 101)),CONVERT(varchar(4), @Year) + ' - ' + DATENAME(mm, CONVERT(datetime, CONVERT(varchar(2), @StartMonth) + '/1/2000', 101)), @StartMonth)

SELECT @MonthKey=SCOPE_IDENTITY() --Get the last inserted MonthKey

SET @StartDay=1

--Set the number of days for the months

IF (@StartMonth=1 OR @StartMonth=3 OR @StartMonth=5 OR @StartMonth=7 OR @StartMonth=8 OR @StartMonth=10 OR @StartMonth=12)
SET @EndDay=31
ELSE IF (@StartMonth=4 OR @StartMonth=6 OR @StartMonth=9 OR @StartMonth=11)
SET @EndDay=30
ELSE
SET @EndDay=28

SET @WeekNoForMonth=1 --set the week no for the month
WHILE @StartDay<=@EndDay --Check all the days are inserted for the Month
BEGIN
IF NOT EXISTS (SELECT * FROM dbo.DimDay WHERE Day=@StartDay AND MonthKey=@StartMonth) --Check whether the processing Day is inserted or not


SET @WeekDayName=CONVERT(varchar(20),DATENAME(WEEKDAY,CONVERT(datetime,CONVERT(varchar(4),@Year)+'-'+CONVERT(varchar(4),@StartMonth)+'-'+CONVERT(varchar(4),@StartDay)))) --Get the processing Day string (Sunday, Monday,...)


INSERT INTO dbo.DimDay
(MonthKey,Day,DayName,WeekNoForMonth, WeekNoForYear)
VALUES
(@MonthKey,@StartDay,CONVERT(varchar(4), @Year)+'/'+CONVERT(varchar(2),@StartMonth)+'/'+CONVERT(varchar(2),@StartDay),@WeekNoForMonth,@WeekNoForYear)

SET @StartDay=@StartDay+1 --Increment the processing day value
IF (@WeekDayName='Sunday') --Check whether the week is completed or not (For this one consider the week is starting from Monday)
BEGIN
SET @WeekNoForMonth=@WeekNoForMonth+1 --If the week is completed increment both the WeekNo values
SET @WeekNoForYear=@WeekNoForYear+1
END


END


SET @StartMonth = @StartMonth + 1 --Increment the Month Value

END
SET @EndMonth=@EndMonth+3 --Increment the end month value for a Quarter
SET @Quarter=@Quarter+1 --Increment the Quarter value
END
END
END
COMMIT TRANSACTION
END TRY
BEGIN CATCH

DECLARE @ErrMsg varchar(1000)
IF @@TRANCOUNT > 0
BEGIN

SELECT @ErrMsg = ERROR_MESSAGE()
ROLLBACK TRANSACTION
PRINT @ErrMsg
END
END CATCH
END

Comments