Post reply

Forcing SQL Agent Jobs to Fail When Remote Procedures Timeout

  • May 2, 2016 at 12:12 am

    #326777

    Comments posted to this topic are about the item Forcing SQL Agent Jobs to Fail When Remote Procedures Timeout

  • May 2, 2016 at 11:52 am

    #1875974

    Thanks for the article.

  • May 4, 2016 at 4:43 am

    #1876521

    This is strange. try the following

    create procedure test1 as return null

    go

    declare @rc int = 991

    exec @rc=test1

    print @rc

    --> you get

    The 'test1' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead.

    0

    So I rather would test for failure (provided 0 stands for success) with something like

    if isnull(@rc,991) <> 0 --> failure

  • May 4, 2016 at 10:01 am

    #1876658

    Thank you for your feedback h.tobisch. That is interesting, good to know that the platform enforces that behavior. Adding the isnull() check would certainly cover other failures, and may be necessary for some procedures. My intention with the try catch is that other failures would raise errors which would be handled in the catch clause. The query timeout always results with NULL as the return code, so we are raising an error based on that specific condition.

    -Chad Feghali

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

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