January 9, 2007 at 1:38 pm
I'm trying to delete a SQL job and I'm getting an error message "Error 8181: Text for: tr_... is mising from syscomments. The object must be dropped and re-created before it can be used."
Does anyone know how I can resolve this issue and delete this job?
January 9, 2007 at 4:39 pm
How are you trying to delete it? And is this SQL Server 2000 or 2005?
-SQLBill
January 9, 2007 at 4:43 pm
Sorry I didn't include that before...
I'm trying to delete this job through Enterprise Manager and we are on SQL 2000.
January 9, 2007 at 10:21 pm
Try using sp_delete_job in Query Analyzer:
Exec
sp_delete_job @job_name = 'Name of Job'
January 9, 2007 at 11:11 pm
this mite be because u dint update te target server properly. if u r not able to delte the job using sp_delete_job the other option is to remove the entry in sysjobs table
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
January 10, 2007 at 7:56 am
I've tried ...
exec
sp_delete_job @job_name = 'Job Name'
...and...
delete
from sysjobs where name = 'Job Name'
... and receive the same error msg for both
Msg 8181, Level 16, State 1, Procedure tr_B773565B-6AB6-4448-9941-9FC44B2ADC0E, Line 65535
Text for 'tr_B773565B-6AB6-4448-9941-9FC44B2ADC0E' is missing from syscomments. The object must be dropped and re-created before it can be used.
January 10, 2007 at 9:40 am
Have you tried dropping and adding back Procedure tr_B773565B-6AB6-4448-9941-9FC44B2ADC0E?
The name makes me think it is a trigger though.
January 10, 2007 at 10:02 am
Thanks everyone for the input, it all helped to figure this out.
This job had a delete trigger on the sysjobs table associated with it that handles cleaning up all the associated jobs tables in the msdb database. The trigger goes through and removes records in the schedule, steps, and other tables when the job is deleted. It's for referential integrity in msdb. Then it also removes itsself at the end, and that is the part that was actually failing and causing the job not to delete.
This delete trigger did not have a record in the syscomments table. I was able to delete the trigger from the table designer in Enterprise Manager, of course after I deleted from job from all it's associated jobs tables. So I guess the delete button forthe trigger in Enterprise Manager bypasses that syscomments check.
January 10, 2007 at 10:34 am
Actually a more efficient method is to just drop the trigger and then run sp_delete_job. All the referential integrity is included in the system sp.
We've got several of these triggers on the sysjobs tables and they are redundant. They were maybe created by some service pack or a transfer of jobs from a previous server? That's just a guess.
January 11, 2007 at 8:07 am
These job triggers were probably created by some backup tools were evaluated last year. All these triggers were for backup jobs. No other type of job had a trigger.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply