Solved

SQL Stored Procedure to Collect Data from All Tables

Posted on 2012-03-23
5
637 Views
Last Modified: 2012-04-11
I am trying to write a service which will monitor a bunch of tables looking for updates etc, and then report out to me what is happening.  

The tables themselves are updated by a variety of services saying what jobs they are doing, e.g. one service will register a job in a table called dbo.JobSchedule, another service will pick this up and update a value "Completed" when finished.  

As I say this happens across numerous tables all the time.  What would be the best way to pull everything out from all of these and store the data in a single one?  The part I'm struggling with is the likes of column ExtractServiceHasUpdated where I need to know if the jobs are the same as the last time it was checked?  I don't want to do a count on jobs as this would go up and down all day.

The following is a brief start I made, however (it is not finished!) it seems rather over complicated and long so far and I'm sure there must be a better way to do it.

CREATE PROCEDURE [dbo].[sJobGetServiceMonitoringDetails]
(
		@DateUpdated DATETIME,
		@ArchiveServiceStatus VARCHAR(10),
		@ExtractServiceStatus VARCHAR(10),
		@JobPageServiceStatus VARCHAR(10),
		@JobPrintServiceStatus VARCHAR(10),
		@ErrorReportServiceStatus VARCHAR(10)
)
AS

DECLARE @ExtractServiceJobs INT
SET @ExtractServiceJobs = (SELECT COUNT(*) FROM dbo.JobSchedule WHERE DataExtracted= 0)

DECLARE @PageNoTotalRegistered INT
SET @PageNoTotalRegistered = (SELECT COUNT(*) FROM dbo.JobAvailablePageNoServices)

DECLARE @PageNoTotalActive INT
SET @PageNoTotalActive = (SELECT COUNT(*) FROM dbo.JobAvailablePageNoServices WHERE IsActive = 1)

DECLARE @PageNoTotalJobs INT
SET @PageNoTotalJobs = (SELECT COUNT(*) FROM dbo.JobPageNumberJobs WHERE IsComplete = 0)

DECLARE @PrintTotalRegistered INT
SET @PrintTotalRegistered = (SELECT COUNT(*) FROM dbo.JobAvailablePageNoServices)

DECLARE @PrintTotalActive INT
SET @PrintTotalActive = (SELECT COUNT(*) FROM dbo.JobAvailablePageNoServices WHERE IsActive = 1)

DECLARE @PrintTotalJobs INT
SET @PrintTotalJobs = (SELECT COUNT(*) FROM dbo.JobPageNumberJobs WHERE IsComplete = 0)


INSERT INTO 
	dbo.JobServiceMonitoring
	(
		ArchiveServiceStatus,
		ExtractServiceStatus,
		ExtractServiceJobs,
		ExtractServiceHasUpdated,
		JobPageServiceStatus,
		JobPageServiceOutstandingJobs,
		JobPrintServiceStatus,
		JobPrintServiceOutstandingJobs,
		ErrorReportServiceStatus,
		PageNumberServiceRegistered,
		PageNumberServiceActive,
		PageNumberServiceTotalJobs,
		PageNumberServiceHasUpdated,
		PrintNumberServiceRegistered,
		PrintNumberServiceActive,
		PrintNumberServiceTotalJobs,
		PrintNumberServiceHasUpdated,
		LastChecked
	)
	VALUES
	(
		@ArchiveServiceStatus,
		@ExtractServiceStatus,
		@ExtractServiceJobs,
		@ExtractServiceHasUpdated,
		@JobPageServiceStatus,
		@JobPageServiceOutstandingJobs,
		@JobPrintServiceStatus,
		@JobPrintServiceOutstandingJobs,
		@ErrorReportServiceStatus,
		@PageNoTotalRegistered,
		@PageNoTotalActive,
		@PageNoTotalJobs,
		@PageNumberServiceHasUpdated,
		@PrintTotalRegistered,
		@PrintTotalActive,
		@PrintTotalJobs,
		@PrintNumberServiceHasUpdated,
		@LastChecked
	)

Open in new window

0
Comment
Question by:angus_young_acdc
  • 2
  • 2
5 Comments
 
LVL 12

Accepted Solution

by:
CmdoProg2 earned 350 total points
ID: 37756946
You may want to consider a different approach (There are pros and cons to this). Using After triggers, update the appropriate column in JobServiceMonitoring table depending on the values in the inserted and/or deleted tables.  Your sJobGetServiceMonitoringDetails procedure would be a select statement.  I would recommend another stored procedure (or an parameter indicator)  that reset the statistics.

Pros:
  The job status statistics are only change based on the data changes and not when monitored.

Cons:
  There may be a delay due to muliple locks on the table.  Requires more coding.  You must have a record in JobServiceMonitoring (unless you have a instead of update trigger on the table which can update or insert the record).

The snippet is for when the JobSchedule table and you separately reset the statistics.  Notice the update only counts the records where the key value has changed.

CREATE TRIGGER dbo.tg_JobScheduleAfter 
   ON  dbo.Jobschedule 
   AFTER INSERT, UPDATE
AS 
BEGIN
  -- SET NOCOUNT ON added to prevent extra result sets
  DECLARE @ExtractServiceJobs int
  SET NOCOUNT ON;
  IF EXISTS(SELECT * FROM deleted)
   BEGIN
    -- UPDATE Action  
    --  REPLACE KeyID with Jobschedule's unique identifier field (use AND if multiple)
    SET @ExtractServiceJobs = (SELECT COUNT(*) 
                                 FROM inserted INNER JOIN
                                      deleted ON inserted.KeyID = deleted.KeyID
                                WHERE inserted.DataExtracted = 0
                                  AND deleted.DataExtracted <> 0)
   END
  ELSE 
   BEGIN
     -- INSERT Action
    SET @ExtractServiceJobs = (SELECT COUNT(*) FROM inserted WHERE DataExtracted = 0)
   END
   
  IF @ExtractServiceJobs > 0
   BEGIN
    UPDATE JobServiceMonitoring
       SET ExtractServiceJobs = ExtractServiceJobs + @ExtractServiceJobs
   END
 
END
GO

Open in new window

0
 
LVL 15

Author Comment

by:angus_young_acdc
ID: 37757563
Hi  CmdoProg2,

Not sure how feasible that would be based on the amount of tables / procedures that already exist, but I shall investigate it.

Out of interest (as my SQL knowledge isn't fantastic), is "deleted" a temporary table in the background or one that I must create?
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 150 total points
ID: 37757569
If you're in SQL 2008, I suggest using Change Data Capture.  You can then use the CDC functions to pull data out if/as you need it.

You really need to write out your *specific* data requirements, too.  That should help you determine what you need.

I'm suggesting CDC because as your data requirements change, you would only have to change your back-end end -- the code that captured the changes on the original tables would never have to be changed and is already coded for you by MS :-) .
0
 
LVL 12

Expert Comment

by:CmdoProg2
ID: 37758830
inserted and deleted tables are created by the trigger and are only available within the trigger instance.  The inserted table contains the resultant values of the affected rows after the insert/update.  The deleted table contains the original values of the affected rows before the update/delete. (Only an update action has both).
0
 
LVL 15

Author Comment

by:angus_young_acdc
ID: 37831860
REally sorry lads I forgot all about this!
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

821 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