SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Kill That Target!


Kill That Target!

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (249K reputation)SSC Guru (249K reputation)SSC Guru (249K reputation)SSC Guru (249K reputation)SSC Guru (249K reputation)SSC Guru (249K reputation)SSC Guru (249K reputation)SSC Guru (249K reputation)

Group: Administrators
Points: 249159 Visits: 19809
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/sjones/killthattarget.asp

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Andy Brooks
Andy Brooks
SSC-Enthusiastic
SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)

Group: General Forum Members
Points: 165 Visits: 2
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.



VegaMachine
VegaMachine
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1288 Visits: 4
Thanks! I've seen this problem before, but I took the easy/bad way out and just recreated the jobs.



- Vega
Steve Jones
Steve Jones
SSC Guru
SSC Guru (249K reputation)SSC Guru (249K reputation)SSC Guru (249K reputation)SSC Guru (249K reputation)SSC Guru (249K reputation)SSC Guru (249K reputation)SSC Guru (249K reputation)SSC Guru (249K reputation)

Group: Administrators
Points: 249159 Visits: 19809
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
sjones@sqlservercentral.com
http://www.sqlservercentral.com/columnists/sjones

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
timoteo
timoteo
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 59
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



Steve Jones
Steve Jones
SSC Guru
SSC Guru (249K reputation)SSC Guru (249K reputation)SSC Guru (249K reputation)SSC Guru (249K reputation)SSC Guru (249K reputation)SSC Guru (249K reputation)SSC Guru (249K reputation)SSC Guru (249K reputation)

Group: Administrators
Points: 249159 Visits: 19809
That will work as well. Thanks for the script.

Steve Jones
sjones@sqlservercentral.com
http://www.sqlservercentral.com/columnists/sjones

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Carl Le Beuvant
Carl Le Beuvant
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 1

Excellent.

I've been experiencing this problem on one of my servers and as usual the MS answer was not viable.

Cheers.


Kenai
Kenai
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 6
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.



Karrasko's Co.
Karrasko's Co.
SSC Veteran
SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)

Group: General Forum Members
Points: 230 Visits: 34

Today I had this problem today. Thanks for the solution. It's easy and simple.

Microsoft team are crazy with his workaround.


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search