Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ruchirj07
Starting Member
37 Posts |
 Posted - 2007-10-08 : 06:09:31
|
| Hi,Below is the piece of code: EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'Add Task to TCM Schedule', @command = @strCommand, @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL',--run operating system command @cmdexec_success_code = 0, @flags = 2, --used for over writing log file @retry_attempts = 5, @retry_interval = 15, --retry at every 15 mins @on_success_action = 4, @on_success_action = 4, @on_fail_action = 4, @on_fail_step_id = 4 Here the @strCommand is the TSQL executing a stored procedure. Even though i'm returning status code = -1001 when stored procedure fails, the job ends successfully and deletes itself as set in the conditions while creating the job.Is there any condition where in we can set the return code of stored procedure = return code of the job?Or is there any other solution to this problem?Any help will be highly appreciated.Regards,Ruchir |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-10-08 : 21:38:56
|
| What do you mean return code of the job? You have to run the job to get job return code. |
 |
|
|
ruchirj07
Starting Member
37 Posts |
Posted - 2007-10-09 : 00:43:07
|
| exactly.When job runs it should return some code if it has passed, failed or hanged... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-10-09 : 00:51:07
|
| This should all be handled inside the job step by T-SQL code and not by the job scheduler. Use variables and IF statements to determine the status of your stored procedure. Then decide whether or not to fail the job or let it succeed.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
|
|
|
|
|
Status Code from Db Job