November 19, 2012 at 7:28 am
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
November 19, 2012 at 7:35 am
(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
November 19, 2012 at 7:52 am
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
November 19, 2012 at 5:03 pm
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.
November 20, 2012 at 1:33 am
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply