Wednesday, May 23, 2007

Cannot add, update, or delete a job that originated from an MSX Server

This is one of those very friendly error messages that don't mean anything when you look at it. This happened to a client who was migrating their servers and backed up all the SQL server Databases and restored them on the new machine they bought. What happened is this, they named the new machine differently than the old machine. When you look at the jobs you can see them (and that’s about it). You can not delete, update or run these jobs. Unfortunately renaming the server was not an option for the client. I looked in the sysjobs table in the msdb database and found out that the name stored was the old server name

SELECT originating_server FROM msdb..sysjobs

I then updated the originating_server fields in the table with the new server name value and everything was fine after that. As far as I know this doesn't effect SQL server 7 since it stores (local) in the originating_server column