3

I deploy multiple SQL Server jobs to a fleet of servers using a PowerShell script. I can create the job steps, success fail action, and even the output file. This all works on multiple servers. What I cannot determine is how to specify that the output file is to be appended to instead of overwritten.

The following is the section from my main script. $Job is populated in a previous step.

$JobStep = new-object ('Microsoft.SqlServer.Management.Smo.Agent.JobStep') ($Job, 'MyJobStepName')
$JobStep.ID = 1
$JobStep.SubSystem = 'PowerShell'
$Command = 'dir d:\'
$JobStep.Command   = $Command
$JobStep.OnSuccessAction = 'GotoNextStep'
$JobStep.OnFailAction    = 'GotoNextStep'
$JobStep.OutputFileName  = 'D:\MyJobFile.txt'
$JobStep.Create()

I have been using this MSDN page as a reference.

I modified one of the steps using SQL Server Management Studio and traced the result. This looks promising. I can issue a SQL command after I create the jobsteps.

EXEC msdb.dbo.sp_update_jobstep @job_id=N'12156dcb-c8cd-4267-901a-55175dd44960', @step_id=1 ,
        @flags=2
  • Not as per this page. – SqlWorldWide Apr 27 '17 at 15:52
  • 1
    I see you already solved your problem. As you already know, SMO calls existing procedures so the documentation for those should be usefull with SMO, in this case it's using sp_add_job_step – Adán Bucio Apr 27 '17 at 16:19
  • 2
    @SqlWorldWide the page that you referenced has it The JobStepFlags enumeration is a list of constant values that specify the file to which to append job history information. – Kin Shah Apr 27 '17 at 16:22
  • 1
    @Kin thank you so much, do not know why I missed it. – SqlWorldWide Apr 27 '17 at 16:28
  • What is SMO? Do you have a reference? – Peter Mortensen Apr 27 '17 at 21:40
5

I think it is more along below lines

$jobStep.JobStepFlags = "AppendToLogFile"
  #call the alter to modify
$jobStep.Alter()
  #display it
$jobStep.JobStepFlags
  • This would be correct. You can find the values associated to this property here. – Shawn Melton Apr 27 '17 at 16:27
1
$jobStep.JobStepFlags = "AppendToLogFile"

or

$jobStep.JobStepFlags = 2

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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