SQL Server send email if no record is inserted in last hour

Question

i am expecting records to be inserted every hour in a table. I want to be able to monitor that table and if no records are inserted in last hour then an email alert should be sent to me.

What is the simplest/best solution? Batch run, monitoring tools or something else?

schedule a task in sql server http://stackoverflow.com/questions/5471080/how-to-schedule-a-job-for-sql-query-to-run-daily ......... send email from sql server http://stackoverflow.com/questions/13300881/how-to-send-email-from-sql-server ...... — Manish Dalal, Apr 28 '14 at 15:15

score 2 · Accepted Answer · edited May 23 '17 at 12:32

Step 1) Create Profile and Account

You need to create a profile and account using the Configure Database Mail Wizard which can be accessed from the Configure Database Mail context menu of the Database Mail node in Management Node. This wizard is used to manage accounts, profiles, and Database Mail global settings

Step 2)

RUN:

sp_CONFIGURE 'show advanced', 1
GO
RECONFIGURE
GO
sp_CONFIGURE 'Database Mail XPs', 1
GO
RECONFIGURE
GO

Step 3)

USE msdb
GO
EXEC sp_send_dbmail @profile_name='yourprofilename',
@recipients='test@Example.com',
@subject='Test message',
@body='This is the body of the test message.
ongrates Database Mail Received By you Successfully.'

To loop through the table

DECLARE @email_id NVARHCAR(450),@id BIGINT,@max_id BIGINT,@query NVARCHAR(1000)

SELECT @id=MIN(id),@max_id=MIN(id) FROM [email_adresses]

WHILE @id<@max_id
BEGIN
SELECT @email_id=email_id 
FROM [email_adresses]

set @query='sp_send_dbmail @profile_name=''yourprofilename'',
                    @recipients='''+@email_id+''',
                    @subject=''Test message'',
                    @body=''This is the body of the test message.
                    Congrates Database Mail Received By you Successfully.'''

EXEC @query
SELECT @id=MIN(id)FROM [email_adresses] where id>@id

END

Step 4)

Expand the SQL Server Agent node and right click the Jobs node in SQL Server Agent and select 'New Job'

In the 'New Job' window enter the name of the job and a description on the 'General' tab.

Select 'Steps' on the left hand side of the window and click 'New' at the bottom.

In the 'Steps' window enter a step name and select the database you want the query to run against.

Paste in the T-SQL command you want to run into the Command window and click 'OK'.

Click on the 'Schedule' menu on the left of the New Job window and enter the schedule information (e.g. daily and a time).

Click 'OK' - and that should be it.

Coppied from here how to schedule a job for sql query to run daily? and here How to send email from SQL Server?

SQL Server send email if no record is inserted in last hour

1 Answers1