Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2002
    Posts
    6

    Unanswered: Problems with errorlevel variable

    Hi,

    I run the following code in the command prompt:

    C:>isql -Utest -Ptest -dtest -b -Q"exit(select * from table1)"

    --

    C:>echo %errorlevel%
    33

    Why is that even when my query has passed, the errorlevel is showing a non-zero value? Also even if I remove the -b option, the errorlevel is again set to a non-zero value. In case I use the query (delete from table1), I see that the errorlevel is set to -101.
    This occurs in SQL Server 7, as well as SQL Server 2000, but does not occur in SQL Server 6.5(it is set to 0 in case of SQL Server 6.5).
    Can anybody please help me in this regard?

    Thanks in advance,
    Regards,
    Vishnu.

  2. #2
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    See BOL "isql Utility"

  3. #3
    Join Date
    Oct 2002
    Posts
    369

    Post Re: Problems with errorlevel variable

    RE:

    I run the following code in the command prompt:

    C:>isql -Utest -Ptest -dtest -b -Q"exit(select * from table1)"

    C:>echo %errorlevel%
    33

    Q1 Why is that even when my query has passed, the errorlevel is showing a non-zero value?
    Note: The following is in regard to osql (however it should hold true for isql, for your issues)

    A1 Probably because the command as written is not logically correct (for the intent described in the post); it passes the query result.

    Note: The -Q used WITH exit(Sql Query) is redundant. To better see that this is so try the following two examples (replace SqlServer with your SqlServer instance name before running) from the command prompt:

    Example One:

    osql -SSqlServer -E -dPubs -b -Q"select Count(*) As 'AuthorsCount' From Authors"

    echo %errorlevel%

    -- it should correctly return the errorlevel

    -- then run the following and compare the results:

    Example Two:

    osql -SSqlServer -E -dPubs -b -Q"exit(select Count(*) As 'AuthorsCount' From Authors)"

    echo %errorlevel%

    -- it should return the count result (not the errorlevel)

    Q2 Also even if I remove the -b option, the errorlevel is again set to a non-zero value.
    A2 Without the -b option, EXIT(sql statement) should normally:
    i run the batch and then,
    ii return the results of the sql statement and then,
    iii quit.

    Q3 Can anybody please help me in this regard?
    A3 Maybe. Did A1 & A2 help?
    Last edited by DBA; 01-07-03 at 22:56.

  4. #4
    Join Date
    Feb 2002
    Posts
    2,232
    I agree with DBA - the switch '-Q' is defined as: "cmdline query" and exit.

    If this does not satisfy your requirements, you can store the query in a file and use the -i option.

  5. #5
    Join Date
    Nov 2002
    Posts
    6

    Re: Problems with errorlevel variable

    Hi,

    Thanks a lot for the help.
    As per A1 it is fine. I removed the "exit" statement & included -Q option and it is fine.

    But I have one doubt still. If I use the -q option (which means I have to explicitly use exit) and also use the -b option, the errorlevel is not appropriately set. But if I use -Q option (which means cmdline query & exit), the errorlevel is perfect. Why is it? Is it that sql 6.5 did not have the 'Q' option or was the processing different?

    Thanks in advance,
    Regards,
    Vishnu.


    Originally posted by DBA
    RE:



    Note: The following is in regard to osql (however it should hold true for isql, for your issues)

    A1 Probably because the command as written is not logically correct (for the intent described in the post); it passes the query result.

    Note: The -Q used WITH exit(Sql Query) is redundant. To better see that this is so try the following two examples (replace SqlServer with your SqlServer instance name before running) from the command prompt:

    Example One:

    osql -SSqlServer -E -dPubs -b -Q"select Count(*) As 'AuthorsCount' From Authors"

    echo %errorlevel%

    -- it should correctly return the errorlevel

    -- then run the following and compare the results:

    Example Two:

    osql -SSqlServer -E -dPubs -b -Q"exit(select Count(*) As 'AuthorsCount' From Authors)"

    echo %errorlevel%

    -- it should return the count result (not the errorlevel)



    A2 Without the -b option, EXIT(sql statement) should normally:
    i run the batch and then,
    ii return the results of the sql statement and then,
    iii quit.



    A3 Maybe. Did A1 & A2 help?

  6. #6
    Join Date
    Oct 2002
    Posts
    369

    Re: Problems with errorlevel variable

    RE: Thanks a lot for the help.

    You are most welcome.

    Q1 But I have one doubt still. If I use the -q option (which means I have to explicitly use exit) and also use the -b option, the errorlevel is not appropriately set. But if I use -Q option (which means cmdline query & exit), the errorlevel is perfect. Why is it?

    A1 One may think of what is occuring as Exit effectively "overriding" -b settings. Exit either returns and passes query results (and only the results, with no errorlevel information per se); or Exit QUITS and returns nothing (hence no result set or meaningful information as to the success, a.k.a. errorlevel, is returned).

    Q2 Is it that sql 6.5 did not have the 'Q' option or was the processing different?

    A2 As with many things between 6.5 (and earlier) <--> 7.0 (and later); the processing is different.
    Last edited by DBA; 01-13-03 at 21:55.

Posting Permissions

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