Thanks for the reply..
Im not sure on how to use sysjobhistory data..
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"
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)
select count(*) from emp;
Please let us know if I can give anymore info...
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: email@example.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.
hist.job_id = job.job_id
job.name = 'Reorganize'
step_id = 0
hist.run_status = 1
hist.run_date = 20121120
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]
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.