need help in redirecting output from osql

  • I am trying to write a batch file where I run an osql command. The restriction is that I cannot work with input files, the query has to be inline. I want to basically run this

    DENY EXECUTE ON sp_Myproc TO [public]

    the Batch File so far looks like this

    :: BatchFile.bat

    :: removes permissions from sp_Myproc

    @echo off

    osql -S . -E -d master -Q "DENY EXECUTE ON sp_Myproc TO [public]"

    IF %ERRORLEVEL% NEQ 0 goto lblError

    echo ... THE BATCH WAS SUCCESSFUL AND RETURNED CODE: %ERRORLEVEL%

    goto end

    :lblError

    echo ... THERE WAS AN ERROR- Return CODE: %ERRORLEVEL%

    :end

    endlocal

    Now when I run this,

    on successful execution of osql

    It works fine and returns error code 0 (from the batch file)

    on failure at osql level (say cannot connect to -d master), I get

    Login failed for user 'DOMAIN\amitsingh'.

    Cannot open database "mastir" requested by the login. The login failed.

    ... THERE WAS AN ERROR WHILE EXECUTING, THE BATCH RETURNED ERROR CODE: 1

    now when OSQL connection is fine, but there is some syntax error (say sp_myproc does not exist) i am not getting any error back on my batch file.

    Msg 15151, Level 16, State 1, Server USEOMAPP1377, Line 1

    Cannot find the object 'sp_Myproc', because it does not exist or you do not

    have permission.

    ... THE BATCH WAS SUCCESSFUL AND RETURNED CODE: 0

    I want this last error also raise the batchfile error...

    any ideas how to do it ?

    -------------------------------------------------
    -Amit
    Give a man a fish and he'll ask for a lemon. Teach a man to fish and he wont get paged on weekends !! :w00t: - desparately trying to fish [/size]

  • Hi Amit

    I guess one of these switches will get you where you wanted. Espacially the last one:

    -m error_level

    Customizes the display of error messages. The message number, state, and error level are displayed for errors of the specified severity level or higher. Nothing is displayed for errors of levels lower than the specified level. Use -1 to specify that all headers are returned with messages, even informational messages. If using -1, there must be no space between the parameter and the setting (-m-1, not -m -1).

    -r {0 | 1}

    Redirects message output to the screen (stderr). If you do not specify a parameter, or if you specify 0, only error messages with a severity level 11 or higher are redirected. If you specify 1, all message output (including "print") is redirected.

    -b

    Specifies that osql exits and returns a DOS ERRORLEVEL value when an error occurs. The value returned to the DOS ERRORLEVEL variable is 1 when the SQL Server error message has a severity of 10 or greater; otherwise, the value returned is 0. Microsoft MS-DOS® batch files can test the value of DOS ERRORLEVEL and handle the error appropriately.

  • Looks like a typo. Take a close look at the error message you posted.

    Cannot open database "mastir" requested by the login

    Did you mistype MASTER?

    Chuck

  • Chuck Lucking (9/23/2009)


    Looks like a typo. Take a close look at the error message you posted.

    Cannot open database "mastir" requested by the login

    Did you mistype MASTER?

    Chuck

    I think that's what he wanted to show: Fatal errors do raise the errorlevel but others do not.

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

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