Why not just build the argument string by hand?
The module's earlier per-function job builders (New-sqmRestoreDatabaseJob, New-sqmAlwaysOnRepairJob, ...) each hand-roll their own argument-string generator for one fixed target function. An earlier generic attempt (Private\New-sqmCmdExecJobStep.ps1) tried to generalize this by embedding parameter values into the generated wrapper via double-quoted string interpolation — it broke on $ characters in paths and carried hardcoded flags (-Verbose -ContinueOnError) that not every function accepts, and was abandoned. New-sqmAgentCommandJob solves this structurally: parameter values are never turned into PowerShell source text at all.
Execution Flow
Two independent choices, both supported: new job vs. appending step(s) to an existing job (
-AppendStep), and on-demand vs. scheduled (-ScheduleType Daily/Weekly/Monthly via New-DbaAgentSchedule, same pattern as New-sqmRestoreTestJob). When appending, the previously-last step's OnSuccessAction is switched from QuitWithSuccess to GoToNextStep automatically — otherwise the job would silently stop before ever reaching the newly appended step(s).Availability Groups: when the instance is an AG replica, the job is created on every node, so it exists everywhere and follows a failover. Combine it with
PrimaryOnly = $true for steps that must only work on the current primary. Use -SkipAlwaysOnPropagation to create it on one instance only.Parameters
| Parameter | Type | Required | Description |
|---|---|---|---|
| -SqlInstance | String | Optional | Target SQL instance where the job is created/extended and where the wrapper/params files are written. Default: $env:COMPUTERNAME. Run this ON the target instance. |
| -SqlCredential | PSCredential | Optional | SQL credential for the connection used to CREATE the job (only needed without Windows-integrated auth / domain trust to the target). Has no effect on how the job step itself authenticates once created. |
| -JobName | String | Required | Name of the Agent job. |
| -Command | Hashtable[] | Required | One or more hashtables describing a step, in execution order: FunctionName (required, must be an exported sqmSQLTool function), Parameters (optional hashtable, splatted), StepName (optional; default is the function name, with a counter _2, _3, … added if that name already occurs in -Command or, with -AppendStep, in the existing job; an explicit StepName that already exists is an error), PrimaryOnly (optional $true: the step only works on the AG primary and ends with success on a secondary). |
| -AppendStep | Switch | Optional | Add the -Command step(s) to an EXISTING job instead of creating a new one. Requires the job to already exist. |
| -Force | Switch | Optional | When NOT using -AppendStep: replace an existing job of the same name. Ignored with a warning if combined with -AppendStep. |
| -ScheduleType | String | Optional | 'None' (default, on-demand only), 'Daily', 'Weekly' or 'Monthly'. |
| -ScheduleTime | String | Optional | Time of day for the schedule, "HH:mm". Default: '02:00'. |
| -ScheduleDays | String[] | Optional | Weekday(s) for -ScheduleType Weekly, e.g. 'Monday','Thursday'. Mandatory for Weekly. |
| -ScheduleDayOfMonth | Int | Optional | Day of month (1-28) for -ScheduleType Monthly. Default: 1. |
| -StartJob | Switch | Optional | Start the job immediately after creating/extending it. |
| -SkipAlwaysOnPropagation | Switch | Optional | By default, if -SqlInstance is a member of an Availability Group, the same job (steps, schedule, -StartJob) is created on ALL other replicas too. This switch limits it to -SqlInstance. On the other replicas an existing job of the same name is replaced (as with -Force); with -AppendStep the step(s) are appended there (if the job is missing on a replica, it is created there with the new step(s) only, with a warning). Parameters.SqlInstance values equal to -SqlInstance are replaced by each replica's own name. |
| -EnableException | Switch | Optional | Throw exceptions immediately instead of logging and returning a result object. |
| -WhatIf / -Confirm | Switch | Optional | Standard ShouldProcess support — dry-runs the entire plan, including allowlist validation, without writing any file or touching the Agent job. |
Return Value
Returns a PSCustomObject with: SqlInstance, JobName, Mode (NewJob or AppendStep), Steps (array of StepId/StepName/FunctionName/ParamsPath), ScheduleName, Started, Replicas (one entry per other AG replica: SqlInstance, Status, Steps, Message), Status (Success, PartialSuccess if a replica failed, or Failed), Timestamp.
Examples
Example 1, Single step, on-demand
New-sqmAgentCommandJob -SqlInstance "SQL01" -JobName "sqmCmd_LoginCompare_AG1" -Command @{
FunctionName = 'Compare-sqmAlwaysOnLogins'
Parameters = @{ SqlInstance = 'SQL01'; AvailabilityGroupName = 'AG1'; OnlyDifferences = $true; FailOnDrift = $true; NoReport = $true }
}Example 2, Two steps chained in one job, scheduled daily at 03:00
New-sqmAgentCommandJob -SqlInstance "SQL01" -JobName "sqmCmd_AGMaintenance" -ScheduleType Daily -ScheduleTime "03:00" -Command @(
@{ FunctionName = 'Compare-sqmAlwaysOnLogins'; Parameters = @{ SqlInstance = 'SQL01'; AvailabilityGroupName = 'AG1'; FailOnDrift = $true } },
@{ FunctionName = 'Repair-sqmAlwaysOnDatabases'; Parameters = @{ SqlInstance = 'SQL01'; NoReport = $true } }
)Example 3, Append a step to that same job later
New-sqmAgentCommandJob -SqlInstance "SQL01" -JobName "sqmCmd_AGMaintenance" -AppendStep -Command @{
FunctionName = 'Get-sqmAlwaysOnHealthReport'
Parameters = @{ SqlInstance = 'SQL01' }
}