SQL - Highlight when no data received

I would like to monitor when data isn't being entered in to a table......yes is not!

We have a system that places data into a database but sometimes this system fails and no data is sent. I have no control over this system but would like to emailed if no data is being received into the table. Data is received every 10 seconds so if there was a gap of say two minutes it would have fallen over. Can this be performed with a SP or trigger or other? Or should this be managed by something else....I hope not!

Regards
S
SimonJohnGAsked:
Who is Participating?
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
Run this to create the table
IF EXISTS(SELECT * FROM sys.tables WHERE name='log')
	DROP TABLE log
GO

CREATE TABLE log (
	id int identity(1,1) PRIMARY KEY NOT NULL, 
	row_count int, 
	dt datetime) 
GO

Open in new window

Then this is the T-SQL to populate the table
Declare @id int 

-- Log the current count
INSERT INTO Log (row_count, dt)
SELECT COUNT (wind_speed), getdate()
FROM [LabView].[dbo].[House2_WeatherStation]
SELECT @id = SCOPE_IDENTITY()

-- Do math to find the change (can do this a number of ways)
Declare @row_count_new int, row_count_old int
SELECT @row_count_new = row_count FROM log WHERE id = @id
SELECT @row_count_old = row_count FROM log WHERE id = @id - 1

SELECT 'Change in number of rows' = @row_count_new - @row_count_old

Open in new window

0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Just off the top of my head ... (I do my own stunts too)
*  Create a 'log' table with a identity field, timestamp and a rowcount
*  Create a Stored proc to
   *    Insert the timestamp and number of rows in that table, returning the identity field as @id
   *    SELECT the rowcount column WHERE the identity = @id MINUS the rowcount column WHERE the identity = @id-1.
   *    IF the above value = 0, do an xp_sendmail  

*  If your company's SQL privs will not allow an xp_sendmail, you'll have to create an SSIS package where...
   *   The SP is executed, and the difference is in the return set, and saved to a variable
   *  The arrow going down from the above SP call has a condition where if @rowcount_difference = 0, it fires a send email task.
*  Use whatever scheduling tool you have available to schedule the above package.
0
 
SimonJohnGAuthor Commented:
I'm pretty new at this, to get the number of rows and timestamp seems ok bu I cant make them work together, most likely very simple. Shown separately below:
Please ignore tmestamp speeling...will correct.

INSERT INTO Log (rows)
SELECT COUNT (wind_speed)
FROM [LabView].[dbo].[House2_WeatherStation]



INSERT INTO Log (tmestamp)
VALUES(GetDate())
0
 
SimonJohnGAuthor Commented:
Hi Jim, great stuff. I think looking good, see below....unless you can see any long term issues?? Just need to create SP and schedule and that should be that :)

Declare @id int

-- Log the current count
USE LabView
INSERT INTO Log (row_count, dt)
SELECT COUNT (wind_speed), getdate()
FROM [LabView].[dbo].[House2_WeatherStation]
SELECT @id = SCOPE_IDENTITY()

-- Do math to find the change (can do this a number of ways)
Declare @row_count_new int, @row_count_old int
SELECT @row_count_new = row_count FROM log WHERE id = @id
SELECT @row_count_old = row_count FROM log WHERE id = @id - 1

IF @row_count_new = @row_count_old BEGIN
USE msdb
 EXEC sp_send_dbmail @profile_name='xxxxxxxx',
 @recipients='xxxxxxxxxxx',
 @subject='LabView - No data updates being received',
 @body='There are no updates being received into the Weather Station Table.
 Please check VI, connectivity, replication.'
 END
0
 
SimonJohnGAuthor Commented:
Your a star!! :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.