• 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.