December 12, 2003 at 7:26 am
Hi,
I have a table sysjobs in msdb. I also have a lot of jobs in my database. There's a column named 'originating_server' that contains the server_name/instance_name. I've copied my data on a new server and would like that server to be the live server. Is there a way in sql to replace the values in 'originating_server' column? For e.g I want to replace:
dynamics\mssql1 to
dynamics2\mssql1
There are over 3000 jobs in that table and it's impossible to go and replace every single one of it manually. Please help!
December 12, 2003 at 7:51 am
Have you thought about just scripting the jobs from the old server to run on the new server? I would think that updating the sysjobs table to reflect a different originating server\instance is going to cause you to be unable to manage the job through the GUI if any changes ever need to be made. Just a thought - could be wrong.
December 12, 2003 at 7:57 am
I think I've read this in a server-migration descussion/whitepaper.
There is a update sysjobs script which puts the new servername in the "originatingserver"column.
If I'm correct all it took was just a simple update statement like this :
update [msdb].[dbo].[sysjobs]
set [originating_server] = newxervername
where [originating_server] = oldservername
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 12, 2003 at 8:10 am
Yes, that would work... if you have a couple of jobs that you don't want to change, then specify a where clause...
The jobs will run successfully as long as everything else, dbnames, table names, etc. remain the same.
**Note: Once you make those changes if you ever need to update the job through sqlagent you will receive a message: error 14274 cannot add, update, or delete a job (or its steps or schedules) that originated from an MSX server... job will not be saved.
So you will be unable to manage the job. Just be aware of this upfront.
December 12, 2003 at 1:30 pm
Hm..just my $0.02 cents on directly updating system tables.
BOL states that you shouldn't do this, because on yre not supported.
Personally I think this technique is an easy, fast and reliable way to foobar SQL Server.
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply