September 23, 2009 at 2:55 pm
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]
September 23, 2009 at 3:41 pm
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.
September 23, 2009 at 3:55 pm
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
September 23, 2009 at 9:19 pm
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