sp_start_job

[rstum2005]

Hi.

How would I go about finding out if a Job has finished by retrieving the Return Code Value 0 (success) or 1 (failure)?

Thanks.

 

[SQLDenis]

lookup sp_help_job

USE msdb
EXEC sp_help_job
Look for current_execution_status

0 Returns only those jobs that are not idle or suspended.
1 Executing.
2 Waiting for thread.
3 Between retries.
4 Idle.
5 Suspended.
7 Performing completion actions.

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[rstum2005]

Thanks.

I looked into this. I do still have questions however.

So if this job fails I wont know by looking at the current_execution_status or would a failing job reside under a 2,3 or 5?

If this job completes is should be 4?

 

[SQLDenis]

Take a look at
last_run_outcome

Outcome of the job the last time it ran:
0 = Failed
1 = Succeeded
3 = Canceled
5 = Unknown


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[rstum2005]

I actually was looking at that but what if the job is executing and you run the query to find out if it has finished or errored and you actually retrieve the info on the last time you ran the job not the current one.

What im trying to do is run a function after the job is finished. If it did not finish because it had an error in one of the sp's than i do not want the next function to complete.

 

[SQLDenis]

Add the function as the next step in the job in this case
In this case if job succeeds go to next step if it fails stop execution
You can set this up in steps/advanced tab

On succes and on failure action

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[rstum2005]

Yea I suppose that may work, I will look into it a bit more.

Thanks for all your help.

 

[rstum2005]

Somewhat of subject but is there a way to run an executible in sql? I tried this below and see it running in the task manager but for some reason it does not show up on the screen.


exec master.dbo.xp_cmdshell '"C:\WINDOWS\system32\notepad.exe"'

 

[rstum2005]

Somewhat off subject but is there a way to run an executible in sql? I tried this below and see it running in the task manager but for some reason it does not show up on the screen.


exec master.dbo.xp_cmdshell '"C:\WINDOWS\system32\notepad.exe"'