Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Does anyone know if it's possible, (and furthermore advisable) to update all cmdexec strings in jobs on a server?

We'd like to change our logging location, and in principle I'd think this could be done very simply by changing the /O "\\LOCATION\file.log" strings.

I can see how you'd do that in fact

USE MSDB
GO
update dbo.sysjobsteps
set [command] = replace([command], '\\OLDLOCATION\... ', '\\NEWLOCATION\... ')
WHERE...

BUT I've had it drummed into me that you should never update the sys tables manually, so the idea of doing this makes me beyond nervous. For anything else I'd use the dbo.sp_update_job proc (http://msdn.microsoft.com/en-gb/library/ms188745.aspx for those who've strayed here looking for something else job related), but that procedure doesn't seem to be able to handle this.

I have a hunch that this might possibly be an exception to the "Never Update the Sys Tables" rule though? I can't imagine that the cmdexec string is implied in any further relations in the way, for example, enabled/disabled status evidently is. Wishful thinking?

So I don't know for sure, and I can't seem to find out. Anyone know or have any experience with this?

Thanks Dan

share|improve this question
    
Do you mean that a job can be enabled separately from the schedule(s) that govern it? That is very much by design and not redundant. –  Aaron Bertrand Mar 19 '13 at 18:54
add comment

2 Answers

up vote 3 down vote accepted

In my opinion, this is one of those operations that would definitely benefit from PowerShell. All you'd need to do is gather the list of job steps that have your old string (in this case, your old location) and replace it with the new string (read: new location).

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") |
    Out-Null

$OldLocation = "<YourOldLocation>"
$NewLocation = "<YourNewLocation>"

$SqlServerName = "localhost"    # by default this will look at the local default instance
$SqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server($SqlServerName)

# loop through all of the SQL Server Agent Jobs on the instance
foreach ($Job in $SqlServer.JobServer.Jobs) {

    # loop through each Job Step on the current Job
    foreach ($JobStep in $Job.JobSteps) {

        # test to see if the Job Step Command text contains your original string
        if ($JobStep.Command.Contains($OldLocation)) {
            $JobStep |
                Select-Object Parent, Name, Command

            # the below two lines would make the change to the new string and apply them
            #$JobStep.Command = $JobStep.Command.Replace($OldLocation, $NewLocation)
            #$JobStep.Alter()
        }
    }
}

NOTE: This is sample code and should thoroughly be tested in a non-production environment. Ensure that your previous data is backed up, and that you are positive the change is the desirable outcome.

All this code does it loops through all your jobs on a specific instance. If the job step command contains a certain string, then it will show you what job it belongs to, the name of the step, and the command containing the string. I have deliberately commented out the command modification lines so that the code only actually identifies which steps contain your string.

You can also narrow your search by adding additional conditional clauses to the if block. For instance, if you just want to check CmdExec jobs, add this with an -and conditional check:

$JobStep.SubSystem -eq [Microsoft.SqlServer.Management.Smo.Agent.AgentSubSystem]::CmdExec

This is simply one way to do this. I will stress my point again: make sure you test out this theory and execution far away from production, and ensure that you can revert changes with the necessary backup.

share|improve this answer
    
thanks, this is great. I'll have a look through this tomorrow and try to get my head around it. I've never used powershell before but a few things have come up lately where it seems like it would be incredibly useful. We're working on a test server doing some investigations on a new server setup at the moment so we're in a good position for me to learn without destroying! –  DanBennett Mar 19 '13 at 17:43
    
Excellent, that is good to hear. PowerShell is becoming a requirement (in my honest opinion) for managing large environments. At the very least, it makes tasks like this relatively simply to accomplish. Good luck, and if you have any questions feel free to ask. –  Thomas Stringer Mar 19 '13 at 17:48
    
Cheers, appreciate it. –  DanBennett Mar 19 '13 at 17:52
    
Just to clarify, this isn't doing anything differently than the set-based UPDATE in the OP's question - it still applies an UPDATE to msdb.dbo.sysjobsteps, it just does it one row at a time. –  Aaron Bertrand Mar 19 '13 at 18:40
1  
@AaronBertrand Indirectly, sure. But running a trace you'll see it's really calling msdb.dbo.sp_update_jobstep. And yes, that stored proc does update the msdb.dbo.sysjobsteps table but there are a handful of other checks that the stored procedure call does. I agree with you, Aaron, but I'll always choose the "API" route instead of direct table DML. –  Thomas Stringer Mar 19 '13 at 19:01
show 2 more comments

I think the UPDATE you plan to do is just fine, assuming your WHERE clause accurately defines only those rows that contain \\OLDLOCATION\, and that you have tested that your REPLACE doesn't remove any false positives (e.g. maybe you have an \\OLDLOCATION\ reference in a comment that should stay intact).

Note that the msdb tables are shipped with SQL Server but they are not "off limits" like system tables (which you can't update in modern versions of SQL Server anyway). These tables are updated directly by SSMS, just like you would do so yourself with your own UPDATE (and just like they would be updated by the PowerShell script Thomas suggested - it doesn't do anything differently other than perform the update one row at a time).

share|improve this answer
    
Thanks for this. Since other time-consuming tasks have popped up meaning I can't indulge in powershell learning at the moment I've gone with the direct update approach. I took a copy of dbo.sysjobsteps first and ran the update with very carefully defined where clause and replace conditions. It seems to have worked fine - we've since run a handful of the jobs with the results we expected. Thanks very much for your advice. Regardless of the approach this time I'll definitely be investigating powershell further in the near future. –  DanBennett Mar 20 '13 at 13:02
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.