Sixth SQL Server Agent deep job step workflow

This article is the sixth of the SQL Server Agent series, please refer to the original text for more information.

As mentioned in the previous series, SQL Server Agent jobs are composed of a series of job steps, each of which is executed by a separate type. Each job step is technically independent, but you can create a workflow that runs through the various steps in the job. In this article, you'll learn how to use a workflow in a job step to change the action you want to perform, and there are multiple conditions that a single job can handle. You will also be exposed to the job step safety and more details of the operating subsystem.
SQL Server Agent Steps
As described in the second article, the SQL Server Agent job contains one or more job steps. Each job step is a completely separate operation that has its own logic for error control, logging, and workflow. When a job includes more than one step, understanding the workflow in the job may cause new problems. Jobs that do not have job steps do not have any actual action (only a small number of warnings).
SQL Server Agent job step workflow
To detect job steps and their workflows, create a new job in SSMs. Under SQL Server Agent, right-click the job and select New job. On the General page, give a job name ("Steps Example" is used in this article). Select the Step page and then click the New button to create the first job step. The new Job Step dialog box appears, as shown in 6.1
Figure 6.1 New Job step
Give a step name (in this case "STEP1"), select the type, which is the job subsystem (we keep the default T-SQL). Type a simple command, such as SELECT * from Sys.tables. Click "Analyze" to verify that you typed a valid T-SQL and you will see the dialog box in Figure 6.2. If there is an error, you will see the result of Figure 6.3. Although the error appears complex, the actual error is at the bottom of the dialog box with a syntax error. You can click on the third button on the left to get more technical details, but this will only give the smo.net a network error diagnosis without additional help.

Figure 6.2 Success Analysis command

Figure 6.3 Parsing command failure
Once through the command analysis, click on the Advanced page and you will see the dialog box in Figure 6.4

Figure 6.4 Job step Advanced Options Page
The Advanced page in the job step is the most workflow in SQL Server Agent. You must choose what you want to do when you succeed, and what you want to do when you fail. When the job step succeeds, you have three choices:
, go to Next
This is the default behavior. If the job step is normal, continue to the next step. If this is the last step of the job, exit the cumulative result of the job report step (if a step fails but the workflow continues, or the report fails).
Quit report successful Jobs
If this step succeeds, the exit job immediately reports that it completed successfully. For example, if the job is performed by a SQL Server Agent alert, the repair may require multiple job steps, and if the previous steps are completed, you can select this option to exit and report that the automatic repair is complete.
Quit reporting jobs that failed
If for some reason you want to quit your job and report a job failure (such as having an incorrect test condition), you can choose this option. Usually there should be a similar job alert set for any necessary follow-up.
Next, you can choose to retry the job step (without reporting an error) after the job fails. For example, if you know that some blocking may cause your job step to fail, you can specify the steps to try 2 times a minute (retry: 2, retry interval (minutes): 1).
The action to perform on failure has the same choice as the successful action described above, but the default is to exit the report failure job.
Because this job is a T-SQL type, you can specify an output file to store the results of the query. You might also tick "append output to existing file" so that you don't lose the previous results, but you need to create a process to truncate the file periodically so that it doesn't consume all of your disk space. Alternatively, you can save the query results to a table in the database. The third option, which contains the step output in the history, is stored in the msdb.dbo.sysjobhistory (originally said to be saved in Msdb.dbo.sysjobstepslogs, but not recorded in the View data table). Ignore the "Run as below" option, which we'll see later.
Click OK, and you will now add a job step. Click OK and you now have a job. Reopen the Steps Example job (that is, right-click the Job menu Selection properties), browse to the job step, click New, and add a second job step. In this step, called "Step2", the job type selects "PowerShell" and then enters the following PowerShell script to see the server operating System properties (Figure 6.5).
GWMI Win32_OperatingSystem | Select osarchitecture,caption
Figure 6.5 Adding a PowerShell type job step
This job step clicks on the Advanced page and selects "Include Step output in history" so you can see the PowerShell script running and viewing the results. Click OK, and then click OK again, and you will see figure 6.6. Step2 will never run because the job exits the report job successfully after STEP1 is running.

Figure 6.6 Warning Job flow logic
Select No, navigate to the Advanced page of the first job step, and change the action to take on success to go to next. You may have noticed something. First, you did not select the "Quit Report successful Jobs" option-how did this happen? When you create the first job step and then create the job, there is only one single job step, so SSMs helps you out and changes the successful operation. When you add a second step, you need to manually adjust the business flow. Each time you add an additional step, you need to return and edit the job.
The second thing to keep in mind is that you can also navigate directly to each step (6.7) In this dialog box. You don't have to go to the next step. You can imagine a job with 10 steps and jump to a different step based on the results of your job steps. This way you can create more complex workflows in your job.

Figure 6.7 Modifying the workflow of Step 1 to the next
Click OK, then the second step will be executed when the job runs.
Now, run the job (right-click the job in SSMs and select Start Job step ...), and then click Start. Once the work has completed successfully, right-click the job again to select View history, you can see the output in the history job step (6.8).

Figure 6.8 SQL Server Agent job history log View step record
Security for SQL Server Agent job steps
When you create a step of type PowerShell, you may have noticed that the "Run as" option is available for the General page of the job step, and the "SQL Server Agent service Account" is selected. When the T-SQL job is connected to SQL Server, it is part of the SQL Server Agent service and will run with the service account. When other subsystems run jobs (such as PowerShell, operating system (CMDEXEC), ActiveX) They actually create a separate process in WINDOWSW, and then connect to SQL Server or the operating system. This process requires a security context to run on Windows, which you can select in the "Run as" option. In the tenth installment of this series we will examine the proxy account and detail how to choose a different security context for the job step.
You may be asked to "run as the following user" under the Advanced Options page of the T-SQL type step. If the owner of a SQL Server Agent job is a member of the sysadmin server role, then the T-SQL job step can run in the context of a different database user, where you can select the user. If the owner of the job is not a member of the sysadmin server role, this option is not available, and the job step runs in the security context of the job owner. This option is available only for the T-SQL subsystem, and the proxy account must be used for other job subsystems.
Next article
The SQL Server Agent job step can provide a complex workflow in a single job. You can go to the front, or skip the job step, or quit the job on success/failure. Each job step can be a completely different job project compared to other steps, and there are several options for the job step log results.
Now you can create interesting jobs and run them, and the next step is to monitor the history of the jobs that are running and running. Therefore, our next article looks at the job Activity Monitor.

Sixth SQL Server Agent deep job step workflow