November 5, 2007 at 2:30 pm
I have windows/dos batch file (test.bat), in which I am trying to get the success/failure status from the sql statements fired thru OSQL, as below
osql -Uuser -Ppwd -Sservername -ifailure.sql -n -b -osqljob_log.log
if %ERRORLEVEL% == 1 GOTO failure
:success
echo 'Success'
GOTO end
:failure
echo 'failed'
:end
In failure.sql, i have the following
exec msdb.dbo.sp_start_job @job_name='Test_Job_Failed'
My "Test_Job_Failed" job, has a store procedure, which have a division by zero error (or some deliberate error).
When i run my Test.bat file, i always get the result as 'success'. What am i doing wrong? How will i get the correct error code..
Appreciate your help.
-Kishore
November 5, 2007 at 2:42 pm
Technically, the osql command IS successful, because sp_start_job will return a success code if it is able to start the requested job. It does not wait for that job to complete and then return its success code. If you want to know if the code that the job is running is going to be successful, you will need to either read the output file from the jobsteps or convert the job to commands that can be run by the .bat file.
Hope this helps
November 5, 2007 at 3:34 pm
We have a lot of sql jobs, with multiple job steps within SQL server. Now we have a external dependency for our jobs from other applications (mainframe/oracle) controled thru cybermation ESP scheduler.
I was hoping that i could use OSQL to check status of these jobs and co-ordinate thier execution with external applications.
Seems like i have to call the stored procedures available in my jobs directly, instead of calling the jobs itself.. which is lot of scripting..
Any other ideas?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply