Post reply

return code from stored procedure in Sql management studio query window

  • September 23, 2011 at 2:11 pm

    #390466

    I have a stored procedure we use for returning suceed or failure code to a commandline batch file.

    I would like to test it in management studio first,

    So I run the stored procedure, and I purposely made the sql agent job run failed, so that the run_job sproc can show me in the message panel in ssms a return code like 1, but i cannot get it.

    what could be wrong with the sproc?

    Here is the statement I excute the sproc,

    EXECUTE [Asset5].[dbo].[RUN_JOB]

    @JOB_TO_RUN='testjob'

    GO

    Then I got the message:

    Job 'testjob' started successfully.

    Msg 50000, Level 16, State 1, Procedure RUN_JOB, Line 103 @CURRENT_RUN_STATUS = 0

    I expected something like return code=1.

    THanks

  • September 23, 2011 at 2:55 pm

    #1386033

    execute @retCode = [Asset5].[dbo].[RUN_JOB] @JOB_TO_RUN='testjob'

    select @retCode

    The probability of survival is inversely proportional to the angle of arrival.

  • September 23, 2011 at 5:05 pm

    #1386062

    Thank you, that works, I got the return number 1.

    Now further step of my project, I would like to use a bat file to call this stored procedure and if the stored procedure failed - returns 1 to the batch file, and the batch file somehow can know the sproc failed.

    Is there a way to do this?

    I thought I may be able to use sqlcmd in the batch file, the hard part is how in the batch can know the sproc failed and also return 1 to the software that calls this batch?

    Thanks

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply