SQL Server job executed by batch file using sqlcmd

Question

I created a batch file to run an SQL job using SQLCMD, but we cannot capture the last status of the job. Please let me know what code we need to write in the batch file.

According to our code, we only have a capture when completing a task, but we want the status of the task to fail or pass. I tried so many things, even those that can capture a magazine, but we only want the latest job status.

The following is the code used in the batch file:

echo execute job sqlcmd -S "servername" -Q "execute msd.dbo.sp_start_job @job_name='Test_Job_Autosys'" -oc:\sqlscript\output.txt if errorlevel 1 exit/b echo job execution completed 

Output:

 Job started successfully 

We can not complete the task, we will not receive or miss; tell us how you can fix the status of the work.

Answer 1

As Nick.McDermaid said. the task starts asynchronously, so it returns without waiting to find out what the status of the task is.

I would suggest you bind the msd.dbo.sp_start_job call in the stored procedure. Thus, you can monitor the status of the task and return or raise an error if the operation failed.

There is an article here that explains this in more detail.

Request Agent job status, execution and waiting for job completion from T-SQL

Hope that helps