?
Solved

SQL - Highlight when no data received

Posted on 2013-12-18
5
Medium Priority
?
199 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 2000 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

801 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