Kill That Target!
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/sjones/killthattarget.asp
-
Steve. This article was of great interest to me as I recently had problems with being able to edit/delete jobs after renaming a server. It seems that in SQL 7 the sp_add_job procedure's default for the @originating_server parameter is '(local)' so it didn't matter if the server was renamed. In SQL 2000 this parameter is set to SERVERPROPERTY('ServerName') if the parameter is supplied as NULL or even '(local)'. When the server is renamed the originating server name is different and so SQL Server considers the job to have been created by a master server.
-
Thanks! I've seen this problem before, but I took the easy/bad way out and just recreated the jobs.
- Vega -
You are welcome. I started to rebuild jobs, but had some time and decided to track this one down. Hopefully this will save some others some time.
Steve Jones
-
Here's another method of fixing sysjobs and sysjobsteps after this problem is encountered.
--after moving MSDB to a diff server, run the folloiwing
use msdb
go
update sysjobs set originating_server = @@servername
update sysjobsteps set server = @@servername where server = 'xxxxx' <<--- old server name
-
That will work as well. Thanks for the script.
Steve Jones
-
Excellent.
I've been experiencing this problem on one of my servers and as usual the MS answer was not viable.
Cheers.
-
Thank you for that, I am in the process of juggling a four SQL server deploy with renaming each one.
Reading the MS solution made me cry
That just saved me a ton of time and headaches. -
Today I had this problem today. Thanks for the solution. It's easy and simple.
Microsoft team are crazy with his workaround.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply