May 24, 2016 at 6:55 am
One of my customers has a good number of SQL Agent jobs which reach out (through appropriate proxy accounts) to UNCs to bring files over for import. The problem is, the server the jobs reach out to is changing, and the new server will have a new name.
It looks like, from some quick Google'ing, that I should be able to script out replacing instances of the server name in the msdb.sysjobsteps table (obviously, I'll test this first with a "junk" job.)
My question is, has anyone else done this, and did it work? Are there any potential "gotchas" I should be on the lookout for?
I'm thinking I should be able to verify the jobs / jobsteps with a simple select:
use [msdb];
go
select *
from sysjobsteps
where command like '%SERVERNAME%';
Then just turn that into an appropriate UPDATE command and go.
May 24, 2016 at 8:03 am
I wouldn't do a direct update statement to the table.
Using the msdb.dbo.sp_update_jobstep SP would be the way to go.
The Redneck DBA
May 24, 2016 at 9:15 am
TheRedneckDBA (5/24/2016)
I wouldn't do a direct update statement to the table.Using the msdb.dbo.sp_update_jobstep SP would be the way to go.
+100
Lot of logic and checks performed in the job related procedures, much better using them rather than trying to brew one's own.
😎
May 24, 2016 at 4:06 pm
Hey all, my apologies for not responding sooner, my PC had snit fits any time I attempted to reply.
I opted to go with Rednecks suggestion and use sp_update_jobstep rather than my original plan. It didn't take much to cobble together a script to grab the needed job steps, REPLACE the server name with the new name, and spit out the required EXEC statements for review and "tweaking." (Some of the steps have single quotes in them...)
Tomorrow I'll try to post up the script.
Once again, thanks!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply