November 8, 2010 at 4:10 am
Hello,
I have a question:
We needed to rename our sql2008 enterprise server on our production site. Everything did go well and it seems that sql server is running verry succesfull.
But I see that the jobs are not running. Looking on the internet did give me the following query:
DECLARE @srv sysname
SET @srv = CAST(SERVERPROPERTY('ServerName') AS sysname)
UPDATE sysjobs SET originating_server = @srv
That query seems to be the solution for sql2000 and sql2005, but not for 2008 the field originating_server is renamed to originating_server_id, and that id should be 0, and the funny thing is, it is 0!
Can anyone help me?
Best regards,
Mischa
November 8, 2010 at 6:07 am
You may find the script posted under "Maintenance Plans" section in this link useful.
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
November 8, 2010 at 11:14 am
did you run:
sp_dropserver 'oldServerName'
sp_addserver 'newServerName', 'local'
if not, please do that then bounce the service.
The probability of survival is inversely proportional to the angle of arrival.
November 8, 2010 at 11:30 am
Script out a drop/create of all your jobs and recreate them all..that will fix it. Whether or not there's a more elegant solution I'm not really sure.
November 9, 2010 at 1:47 am
Thanks for all of your help, I did run the script as mentioned above, and most problems where fixed, I just needed to recreate one script!
Best regards,
Mischa
November 9, 2010 at 2:58 pm
Mischa E.J. Hoogendoorn (11/8/2010)
. Looking on the internet did give me the following query:DECLARE @srv sysname
SET @srv = CAST(SERVERPROPERTY('ServerName') AS sysname)
UPDATE sysjobs SET originating_server = @srv
That query seems to be the solution for sql2000 and sql2005, but not for 2008 the field originating_server is renamed to originating_server_id, and that id should be 0, and the funny thing is, it is 0!
that was the fix pre 2005 only (SQL2000 and probably SQL7)
I always thought it a shame MS did not provide a fix for this situation. Must have mucked up a lot of peoples DR plans!.
---------------------------------------------------------------------
November 9, 2010 at 10:24 pm
Like mentioned earlier this article has a script for fixing Maintenance Plans.
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
November 10, 2010 at 7:15 am
yes I saw that but its not an MS supplied script.
---------------------------------------------------------------------
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy