Solved

SQL - Highlight when no data received

Posted on 2013-12-18
5
193 Views
Last Modified: 2013-12-24
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
Comment
Question by:SimonJohnG
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39726536
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
 

Author Comment

by:SimonJohnG
ID: 39727216
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
 
LVL 66

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 39727246
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
 

Author Comment

by:SimonJohnG
ID: 39728793
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
 

Author Closing Comment

by:SimonJohnG
ID: 39737524
Your a star!! :)
0

Featured Post

Is Your DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question