Scheduled Jobs and moving a database to a new server 1

[DABEERMAN]

Recently my entire SQL Server database was moved from one physical machine to another physical machine. During this process the server was renamed. The problem I am having is that now all of my scheduled DTS jobs no longer work. The error reads "SQL Server does not exist or access denied". I noticed that the "source" on the general tab of the scheduled job properties had the old server name and that I could no longer edit or delete the jobs. To correct this I updated the originating_server in sysjobs to reflect the new server name and could then edit the jobs without problem. If I kick off the DTS packages manually they work fine but the scheduled jobs all fail.

I have checked the account that is used for the SQLAgent service on the new server. It has the appropriate permissions and everything looks fine. I have also checked the client network utility and made sure the box name is defined correctly. It is also defined correctly on SQL.

I did do some testing and recreated some of the schedules to make them identical to the existing schedules (except for the schedule name of course) and they run fine. I'm baffled, I may end up having to recreate all of the schedules. In the meantime I have been manually running all job which is a major pain. I'm stumped, any ideas? Any help would be greatly appreciated. TIA.

 

[tlbroadbent]

It is likely that the old machine name is still in msdb..sysjobs table. You can change the machine names for the jobs the following SQL statement. Warning: Hacking system tables is not supported and should be done with care. However, this is relatively safe. ;-)

UPDATE sysjobs
SET originating_server = @@servername
WHERE originating_server <> @@servername Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[DABEERMAN]

Thanks for the advice Terry, unfortunately I already tried updating the sysjobs table (which did have reference to the old server) prior to posting this issue but this did not resolve the issue. After updating the sysjobs table I was able to make edits to the properties of each scheduled job but the jobs still do not run the error message is still the same, &quot;SQL Server does not exist or access denied.&quot;

Any more suggestions?