Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2002
    Posts
    1

    Angry Unanswered: oSql Error Handling

    Can anyone please tell me why my error handling does not work. I am using the following code in a script file which I pass as an input file to oSql:

    IF (@@ERROR <> 0)
    EXIT(1)
    ELSE
    EXIT(0)

    I have also tried labels as written below to no avail:
    IF (@@ERROR <> 0) GOTO error_control

    success_control:
    EXIT(0)

    error_control:
    EXIT(1)


    If anyone knows the solution could they please let me know.

  2. #2
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    I've just written a little script as well and it seems that 'EXIT' must be the last statement within the script.

    Example:
    Code:
    SET NOCOUNT ON
    
    DECLARE @rc int
    SET @rc = 0
    
    IF ((SELECT DB_NAME()) = 'pubs') BEGIN
       SET @rc = 99
       GOTO COMMON_EXIT
    END
    
    SELECT DB_NAME()
    
    COMMON_EXIT:
    EXIT(SELECT @rc)
    GO
    The EXIT that will set the MS-DOS ERRORLEVEL variable.

    Example (1)
    C:\Work>osql -E -Styche -isctest.sql -n -h-1
    master

    0

    C:\Work>echo %errorlevel%
    0

    C:\Work>


    Example (2) against Pubs
    C:\Work>osql -E -Styche -isctest.sql -n -dpubs -h-1
    99


    C:\Work>echo %errorlevel%
    99

    C:\Work>


    Also one thing about @@ERROR, it is affect by all T-SQL statements.

    Example
    Code:
    UPDATE blah blah blah
    
    IF (@@ROWCOUNT = 0) BEGIN
        IF (@@ERROR = 0) 
             SET @RC = 0
        ELSE
             SET @RC = 1
    END
    @@ERROR will always be 0, since the IF statement is successful it will override the @@ERROR value set by the UPDATE statement.

    Code:
    UPDATE blah blah blah
    
    SELECT @Rows = @@ROWCOUNT,
            @Err = @@ERROR
    
    IF (@Rows = 0) BEGIN
        IF (@Err = 0) 
             SET @RC = 0
        ELSE
             SET @RC = 1
    END

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •