modify system stored proc 1

[ktucci]

is there anyway to modify a system stored proc...if so, how...?

any help will be greatly appreciated

 

[JayKusch]

You should NOT modify any system stored procedure. The ramifications can be devistating!!!

Thanks

J. Kusch

 

[ktucci]

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.

 

[JayKusch]

When does 'sp_purge_jobhistory' get fired off. Just for my FYI.

Thanks

J. Kusch

 

[ktucci]

i know it happens at least when a job is deleted...

 

[JayKusch]

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?

Thanks

J. Kusch

 

[ktucci]

my mistake the sp i need to modify is:

sp_delete_job

 

[ktucci]

any job can be set to delete after it is run, which the ones i am creating are set to do...so there is a disconnect from my app and the jobs

 

[SQLBill]

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.

-SQLBill

 

[ktucci]

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

 

[JayKusch]

So ... doing the INSERT/SELECT will not work either? Why?

Thanks

J. Kusch

 

[ktucci]

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


thanks for the help

 

[JayKusch]

Here is a link as to WHY not to mess w/ system stored procedures straight from Microsoft ...

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sys_00_690z.asp

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.

Thanks

J. Kusch

 

[ktucci]

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

thanks for the help

i am open to any and all suggestions

 

[JayKusch]

Have you tried an ALTER PROCEDURE statement yet?

Thanks

J. Kusch

 

[ktucci]

no i havent....let me give that a shot

 

[ktucci]

you got it...'ALTER PROCEDURE' worked like a charm


thanks a million

 

[vongrunt]

Hm... what about trigger FOR DELETE on msdb.dbo.sysjobhistory?

 

[JayKusch]

GREAT!!! ... just hope we don't see a post of ...

"MY SYSTEM CRASHED HARD: all I did was changed a System SP!"

LOL

Have a great day!

Thanks

J. Kusch