Modify Job name

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Modify Job name

Modify Job name

Rate Topic

Display Mode

Topic Options

Author

Message

balasach82

Posted Friday, January 11, 2013 7:01 AM

Old Hand

Group: General Forum Members
Last Login: Monday, May 13, 2013 8:55 AM
Points: 307, Visits: 743

How to rename all the job in an sql server 2005? I need to add the servername to the jobnames?

Post #1405990

Marbo

Posted Friday, January 11, 2013 7:47 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, January 24, 2013 3:52 AM
Points: 8, Visits: 42

Hi balasach82,

Lets say i have a job that is called Backup and my servername is BI-ONT this could be the query that you could use.

Update msdb..sysjobs
Set name = 'BI-ONT'+'_'+ name
where name = 'Backup'

If you don't use the Where clause de statement will update all your jobs.
Don't forget to refresh your object explorer after all of this.

Marbo

Post #1406036

anthony.green

Posted Friday, January 11, 2013 7:53 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

Don't update the system tables directly.

Instead use sp_update_job passing in the old name and the new name.

Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1406043

balasach82

Posted Friday, January 11, 2013 8:23 AM

Old Hand

Group: General Forum Members
Last Login: Monday, May 13, 2013 8:55 AM
Points: 307, Visits: 743

Yes, i didnt want to update the systables directly. thats why wanted to find the best way to do this.

Running the sp_update_job is the best method and no other "Accepted" way. Is it?

Post #1406069

anthony.green

Posted Friday, January 11, 2013 8:26 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

Yep, thats the documented way to do what you want to do.

Post #1406075

balasach82

Posted Saturday, January 12, 2013 2:36 PM

Old Hand

Group: General Forum Members
Last Login: Monday, May 13, 2013 8:55 AM
Points: 307, Visits: 743

Got below error:
Cannot enable the Service Broker in database "msdb" because the Service Broker GUID in the database (XXXXX....) does not match the one in sys.databases (YYYYY.......).

I dont have an clue whether the master/msdb was restored from another server.
Can i create a new service broker. For this any precautions needed like i have to check all the existing service broker etc?

Post #1406416

anthony.green

Posted Monday, January 14, 2013 1:22 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

ALTER DATABASE XXX SET NEW_BROKER WITH ROLLBACK IMMEDIATE

First stop everything that uses service broker in MSDB, like DBMail or anything you have created manually.

Post #1406586

« Prev Topic | Next Topic »

Permissions