SQL Server job run by batch file using sqlcmd

Question

I have created batch file for trigger SQL job using SQLCMD but we are not able to capturing the last status of job run. Please let me know what code we need to write in batch file.

As per our code we have capture only when job is run but we want status of job is failed or pass. I have tried so many thing even able to capture the log but we want only last status of job.

Below is the code using in batch file:

echo execute job
sqlcmd -S "servername" -Q "execute msd.dbo.sp_start_job @job_name='Test_Job_Autosys'" -o c:\sqlscript\output.txt

if errorlevel 1 exit/b

echo job execution completed

Output:

Job started successfully

We are not able to capture job is fail or pass; please let us know how can be capture the status of job.

sp_start_job runs asynchronously. That is to say it starts the job then returns immediately. It does not wait for the job to finish. Please explain further what you're trying to do and we can suggest an alternate method. — Nick.Mc
– Nick.Mc, Commented Aug 7, 2015 at 3:30
Take a look into sysjobhistory, maybe that's what you need: msdn.microsoft.com/en-us/library/ms174997.aspx — James Z
– James Z, Commented Aug 7, 2015 at 4:47

Spock · Accepted Answer · 2015-12-31 09:47:56Z

2

Like Nick.McDermaid said. the job is started asynchronously, so it returns without waiting to find out what the status of the job is.

I would suggest you wrap the call to msd.dbo.sp_start_job in a stored procedure. That way you can monitor the job status and return or raise an error if the job failed.

There's an article here that explains it in more detail.

Querying agent job status, executing and waiting for job completion from within T-SQL

Hope that helps

answered Dec 31, 2015 at 9:47

Spock

4,9802 gold badges19 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Server job run by batch file using sqlcmd

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related