Solved

SQL Stored Procedure to Collect Data from All Tables

Posted on 2012-03-23
5
621 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:ScottPletcher
ScottPletcher 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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.
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…

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now