Can this be done inside sql 2000

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Can this be done inside sql 2000

Author

Topic

tisbris
Starting Member

5 Posts

Posted - 2007-06-13 : 14:32:43

I have this program that collects data and send these data to a sql database, but a very important function is missing is this program - a function, that verify that all data has been collected.

My quistion - is it possible to make some kind of query based on the existend tables inside sql and return the result(alarm) to a logfile?

Can this query be set to run every hour?

Is it possible to make a query, that looks at ex. the last (full) hour, and checks for missing data from these tables:

[TrendLog]
TrendLogId - IntervalSeconds
1 - 15
2 - 60

[TrendLogValue]
TrendLogId - Sequence - LogTime
1 - 1 - 24-04-2007 18:00:15
1 - 2 - 24-04-2007 18:00:30
1 - 3 - 24-04-2007 18:00:45
1 - 4 - 24-04-2007 18:00:60
2 - 1 - 24-04-2007 18:00:15
2 - 2 - 24-04-2007 18:01:15
2 - 3 - 24-04-2007 18:02:15
2 - 4 - 24-04-2007 18:03:15

[TrendLog]-IntervalSeconds, indicates the interval between each record ex. 15 means 240 records each hour and 60 means 60 records each hour.

[TrendLogValue]-Sequence, indicates a unik number for each record for each trendlogid, if data is missing the table would look like this:

[TrendLogValue]
TrendLogId - Sequence - LogTime
1 - 1 - 24-04-2007 18:00:15
1 - 2 - 24-04-2007 18:00:30

1 - 3 - 24-04-2007 18:00:60
2 - 1 - 24-04-2007 18:00:15

2 - 2 - 24-04-2007 18:03:15

I would be VERY happy if someone could help me out here, but I'm new to SQL queries, so please be gentle

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-13 : 14:46:50

(1) You could do a COUNT of the records for each TrendLogId and if its anything but 240 or 60 raise an error or write to event log
(2) Create a job that runs every hour. If you have a SELECT for the above statement, you can set up email notification if the COUNTs dont match.

Start with the query to identify records that have a gap. The rest should be pretty easy.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

tisbris
Starting Member

5 Posts

Posted - 2007-06-13 : 15:09:14

thx for the quick reply...

as I said earlyier, I'm new to SQL queries, so if one of you could help me out... please

Thx again

tisbris
Starting Member

5 Posts

Posted - 2007-06-14 : 16:01:42

I have now made 2 queries, and the result of VIEW2 return the values that i need.

Could you please have a look at these 2 queries, and see if they could be stored in 1 query only.

VIEW1:
SELECT COUNT(DISTINCT dbo.TrendLogValue.Sequence) AS [Log Sidste døgn], dbo.TrendLogValue.TrendLogId, dbo.TrendLog.IntervalSeconds,
dbo.TrendLog.TrendLogGuid
FROM dbo.TrendLogValue INNER JOIN
dbo.TrendLog ON dbo.TrendLogValue.TrendLogId = dbo.TrendLog.TrendLogId
WHERE (dbo.TrendLogValue.LogTime > GETDATE() - 1)
GROUP BY dbo.TrendLogValue.TrendLogId, dbo.TrendLog.IntervalSeconds, dbo.TrendLog.TrendLogGuid

VIEW2:
SELECT [Log Sidste døgn], IntervalSeconds, TrendLogId, TrendLogGuid, [Log Sidste døgn] - 86400 / IntervalSeconds AS FejlLogDiff
FROM dbo.VIEW1
WHERE ([Log Sidste døgn] - 86400 / IntervalSeconds <> 0)

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-14 : 16:19:36

You just need a count right? Why do you need to put all the other columns in it ?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

tisbris
Starting Member

5 Posts

Posted - 2007-06-14 : 16:22:15

no, i need all the data, that the query VIEW2 returns....

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-14 : 16:30:09

Also you need a place to store the lastcounted time or something like that. If for some reason the job stops or is disabled when you run it the next time it can always read the previous value and count from there on.
In the simplest form it could be like this:

 select  TrendLogId , count(*)
from @t 
WHERE LogTime >= LastCountedTime
group by TrendLogId
having count(*) <> 4

This will identify all the TrendLogId's that have a missing value.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

tisbris
Starting Member

5 Posts

Posted - 2007-06-14 : 16:45:01

where does this sql go... new view?? or inside view2??

i tried a new view, but it couldn't run...

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-14 : 17:11:03

No need for any view. Create a job and put this SQL in it. Under Step type choose T-SQL. Before you create the job think about where you will store this information returned by the SELECT? Do you want to create a table and dump the results into it? The job can also email the results. But do you want to receive an email every hour? Read up books on line on how to create jobs.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources