• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 201
  • Last Modified:

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
0
SimonJohnG
Asked:
SimonJohnG
  • 3
  • 2
1 Solution
 
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
 
Jim HornMicrosoft 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
 
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now