Forcing SQL Agent Jobs to Fail When Remote Procedures Timeout

  • Chad Feghali

    SSC Veteran

    Points: 227

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

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the article.

  • h.tobisch

    SSCommitted

    Points: 1671

    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

  • Chad Feghali

    SSC Veteran

    Points: 227

    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 4 (of 4 total)

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