How to programatically change a SQL Server Job's command

My project does not have a DBA.  at all.  None.  That's rather annoying, because the dba responsibilities have fallen to me.  Don't get me wrong.  I have thoroughly enjoyed the work I have been doing.  Getting the SQL Server Merge Replication to work in our environment has been an enjoyable, challenging experience.  But, I am looking forward to the replication work stabilizing so that I can move back to .Net development.

Anyway...

The purpose of this post is to document how I went about using T-SQL to change the command of a SQL Server Job. 

We already had a script that produced the SQL Server merge replication subscription and its associated Job.  Our network connectivity is so poor, though, that the replication job routinely timed out.  So, we decided to copy the publication's snapshot files to a folder on the subscription client.  This is referred to as an Alternate Snapshot Folder.

In order for the subscription to use that folder, both the subscription and the job must refer to it.  The sp_addmergepullsubscription_agent stored procedure has an @alt_snapshot_folder parameter which can be used to tell the subscription where to place the files.  Even though the stored procedure creates the job, it does not inform the job about that setting.

To force the job to use the Alternative Snapshot Folder, the job's command must be altered.  The following SQL appends the "-AltSnapshotFolder" item to the job's command if the command does not already have AltSnapshot

-- Update the Job to ensure it knows about the alternate snapshot folder.
DECLARE @PublicationSeachKey as nvarchar(100);
SET @PublicationSeachKey = N'%[' + @publication + ']%'

UPDATE msdb.dbo.sysjobsteps
    SET command = command + ' -AltSnapshotFolder [' + @altLocation + ']'
FROM
        msdb.dbo.sysjobs j
JOIN    msdb.dbo.sysjobsteps js ON (js.job_id = j.job_id)
JOIN    master.dbo.sysservers s ON (s.srvid = j.originating_server_id)
WHERE   
    j.name = @JobName
    AND
    js.command LIKE @PublicationSeachKey
    AND
    js.command NOT LIKE '%AltSnapshotFolder%'
   
GO