Solved

SQL - Highlight when no data received

Posted on 2013-12-18
5
188 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
  • 3
  • 2
5 Comments
 
LVL 65

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 65

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
T-SQL 10 35
Upgrading my SSIS package in VS 2012 6 59
Migration from SQL server to oracle (XML input) 4 23
HTML <font style="color:red"> 9 30
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

839 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