Hi @kkran You could create job to send alert using sp_send_dbmail based on the max load_datetime.
The query will be like this:
USE DatabaseName
GO
DECLARE @load_datetime datetime
SELECT @load_datetime=MAX(load_datetime)
FROM TableName
IF DATEDIFF(HOUR,@load_datetime,GETDATE()) >= 32
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@recipients=N'******@xxxxx.com',
@body='32 hours no file loaded',
@subject ='32 hours no file loaded',
@profile_name ='xxxxx',
@query = '(select top(10) * from DatabaseName.dbo.TableName order by load_datetime DESC)'
END
Then create a scheduled job that will check every hour if the time difference is greater than 32 hours.
Best regards,
Cosmog Hong
If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.