Kill That Target!

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Steve Jones » Kill That Target!

Kill That Target!

Rate Topic

Display Mode

Topic Options

Author

Message

Steve Jones - SSC Editor

Posted Tuesday, September 03, 2002 12:00 AM

SSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 3:30 PM
Points: 31,436, Visits: 13,751

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

Post #6571

Andy Brooks

Posted Monday, September 16, 2002 3:01 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, June 04, 2009 4:01 AM
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.

Post #40909

VegaMachine

Posted Monday, September 16, 2002 8:27 AM

Ten Centuries

Group: General Forum Members
Last Login: Wednesday, December 15, 2010 1:20 PM
Points: 1,060, Visits: 4

Thanks! I've seen this problem before, but I took the easy/bad way out and just recreated the jobs.

- Vega

Post #40910

Steve Jones - SSC Editor

Posted Monday, September 16, 2002 10:34 AM

SSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 3:30 PM
Points: 31,436, Visits: 13,751

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

Post #40911

timoteo

Posted Monday, December 09, 2002 11:34 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, August 23, 2011 10:54 AM
Points: 4, Visits: 52

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

Post #40912

Steve Jones - SSC Editor

Posted Monday, December 09, 2002 1:37 PM

SSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 3:30 PM
Points: 31,436, Visits: 13,751

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

Post #40913

Carl Le Beuvant

Posted Monday, March 07, 2005 4:24 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, May 10, 2007 3:54 AM
Points: 3, Visits: 1

Excellent.

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

Cheers.

Post #165979

Kenai

Posted Monday, April 16, 2007 6:36 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, March 07, 2008 10:17 AM
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.

Post #358765

Karrasko's Co.

Posted Thursday, May 10, 2007 3:31 AM

Valued Member

Group: General Forum Members
Last Login: Wednesday, April 29, 2009 2:16 AM
Points: 54, Visits: 23

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

Microsoft team are crazy with his workaround.

Post #364647

« Prev Topic | Next Topic »

Permissions