Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Getting sql server job status into a table Expand / Collapse
Author
Message
Posted Monday, November 19, 2012 7:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, October 5, 2013 8:14 AM
Points: 5, Visits: 110
Hi Guys,

1) I need to insert a single job status after completing every run into a new table

and

2) Total row count of single table into another new table .

Could any one of you give me thoughts on how to do it..

im kind of struct , cant think of how to do it..

thanks a lot for your time
Post #1386394
Posted Monday, November 19, 2012 7:35 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:50 AM
Points: 5,367, Visits: 9,913
(1) This information is already stored in sysjobhistory in msdb.
(2) Use the COUNT agrregrate function.

If you need more detailed help than that, please specify your exact requirement, including table DDL, sample data and expected results.

John
Post #1386404
Posted Monday, November 19, 2012 7:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, October 5, 2013 8:14 AM
Points: 5, Visits: 110
Hi John,
Thanks for the reply..

Im not sure on how to use sysjobhistory data..
and

My request is job named "reorganize" which is in scheduled for daily at 8 Pm.
User want to know the wheather job has failed or successfull..

for this, we want to create a new table and insert the status like below

for this expected results should be

select * from jobstatustable
====================
"Job name" "Status"

reorganize Success

and

2nd request is .. Just want to select row count of emp tabel for example ( User should'nt be granted any access to source table , thats why we want to create new table and insert the total rows of source table into destination table)

ex:

select count(*) from emp;
===========

Row count

1056
.

Please let us know if I can give anymore info...

Thanks
Post #1386409
Posted Monday, November 19, 2012 5:03 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, April 27, 2014 8:38 PM
Points: 90, Visits: 420
satishdbaid (11/19/2012)
Hi John,
Thanks for the reply..

Im not sure on how to use sysjobhistory data..
and

My request is job named "reorganize" which is in scheduled for daily at 8 Pm.
User want to know the wheather job has failed or successfull..

for this, we want to create a new table and insert the status like below

for this expected results should be

select * from jobstatustable
====================
"Job name" "Status"

reorganize Success

and

2nd request is .. Just want to select row count of emp tabel for example ( User should'nt be granted any access to source table , thats why we want to create new table and insert the total rows of source table into destination table)

ex:

select count(*) from emp;
===========

Row count

1056
.

Please let us know if I can give anymore info...

Thanks



I know this is not exactly what you are trying to do but there are simpler ways to get the same result. If it isn't what you want then can look at doing the options you've requested (i.e. New table etc with results) . Once again I don't know exact reason or who is accessing the reports so just going on an assumption and what access levels they will have.

So as you have setup a scheduled SQL Server Job Agent for 8PM you can add a new operator which sits under SQL Server Agent (e.g. Name: ReorganiseJobResults & for the Emailname: jsmith@testemail.com)

Once you have set this up you can then simply right click on the SQL Job itself (Reorganize) go to Properties, Notifications, check the email box, select the operator "ReorganiseJobResults" and choose for an email when the Job Fails, Succeeds or completes. An email will get set with the results.


The other thing mentioned by the previous user for Sysjobhistory is found in the msdb database. you can do something like the below code just need to modify it to your needs.

SELECT 
hist.Instance_id,
hist.run_date,
job.name,
hist.run_status
FROM
[msdb].[dbo].[sysjobhistory] hist
INNER JOIN
[msdb].[dbo].[sysjobs] job
ON
hist.job_id = job.job_id
WHERE
job.name = 'Reorganize'
AND
step_id = 0
AND
hist.run_status = 1
AND
hist.run_date = 20121120
ORDER BY
hist.Instance_id DESC



With the row count is this a once off after the job has run? have you thought about making a simple SSRS report that the user can run adhoc when required or sent to the user at certain time of the day via subscription? however If you want to do the insert to a new table you would have to just do

INSERT INTO [schema].[tablename] 
(
column1,
column2,
n...
)

SELECT
column1
column2
n....
FROM
sourcetable
WHERE
condition1 = ??? etc...


then once its in that table then you can do your count?

there probably are multiple ways and better ways to do it but this is just one of many... people will prob disagree with this approach.
Post #1386637
Posted Tuesday, November 20, 2012 1:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, October 5, 2013 8:14 AM
Points: 5, Visits: 110
Hi Tava,

Thanks for the reply

I too thought about the adding mails to operators but senoir collegue dont want this ( bacause if we do for this one they may ask for all other jobs, thats his concern ).

We dont use SSRS on our envirnment.

I will try ur suggestions on test env ,

other thoughts are really helpful.

Thanks
Post #1386721
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse