i know, but i need to pull some data from the table 'sysjobhistory' before it gets purged by 'sp_purge_jobhistory'. basically i am creating and deleting jobs dynamically using DMO and i need the job history prior to it being purged. i just need to be able to modify 'sp_purge_jobhistory' to dump the data for the given job_id prior to getting dropped.
So ... if I may assume ... you are deleting theses jobs manually based on the job_id. Can you not create at table and INSERT/SELECT the records from the sysjobshistory table BEFORE you delete the job?
Why don't you copy the system stored procedure, make your change(s) and change the name?
I did this with sp_spaceused. I copied it, made some changes and renamed it usp_spaceused (usp for user stored procedure). I saved it in my user database and run it from there.
unfortunately i still need it to be a system stored proc after i modify it and it still needs to be called as it noramlly is from a the process of deleting a job...
i have this OCX that runs inside of our biling system that allows users to choose jobs and billing periods to run. by choosing a billing period, i then need to relate the billing period info to the job. so i use DMO and basically have a template job that sits out there and never gets run but serves as a template for creating these dynamic jobs. so after i have the billing period and job, i read the job script into a string and replace my variable (@billrunid) inside of the job. now i take this string and execute it but change the job name to the old job name followed by the variable that i passed in, thus creating a new job with a unique name. also keep in mind i set the template job that created this job to have the delete upon completion flag set. so after creating the new job i then start the job, which runs and deletes itself. well, sql server has no way of keeping job histories for deleted jobs so this is where my orginal question came from. i need a way to pull the history prior to the job being deleted which happens upon completion. simply adding the folling between the ---*** to the sp_delete_job sp will resolve the problem:
IF (@delete_history = 1)
---***
insert into comx_kt.dbo.clext_ihwy_jobexecutor_sqljobhistory
select *
from msdb.dbo.sysjobhistory
WHERE job_id IN (SELECT job_id FROM #temp_jobs_to_delete)
----***
DELETE FROM msdb.dbo.sysjobhistory
WHERE job_id IN (SELECT job_id FROM #temp_jobs_to_delete)
so basically modifying the system stored proc seems like the only way to accomplish this task. i just need to know if modifying system stored proc is possible and if so, how...?
in relaying that ... you may be able to open the SP up, copy it, delete the SP and then recreate it in Query Analyzer by pasting it in and makeing your changes.
I personally have not done this nor do I in any way suggest this. Let us know if you were able to create the SP just for our FYI.
i tried that orgianlly...sql server does not let you delete system objects...i am not sure if there is modifiable security setting to allow this but it did not work for me
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.