How can i get a return code of 1 for an osql command which has a lower severity..

I am running the following OSQL command and capturing the return code
for the error .Whenver i have an error like server not exists or uable
to login I get a return code of 1 for the %ERRORLEVEL%.However
whenever I have an errorof a wrong dbcompatibility error the retun
code id 1 even though sql returns an iformation message from OSQL that
the right copatibilty levels are 60,70 and 80.How can i get OSQL to
return the right return code whenver a error of this type occurrs from
batch mode sql.The OSQL i am running from the batch is

osql -S%SrvName% -U%Username% -P%Userpswd% -n -w 132 -d%DBname%
-Q%sqlcmd% -o%Dirrpt%\%DBname%_%SPname%.txt
ECHO %errorlevel% >> %logbatch%
IF %ERRORLEVEL% NEQ 0 Goto SQLError

sqlcms is exec sp_dbcompatibiltylevel srvrname, dbname 80

Thanks in anticipation.

Ajay[posted and mailed, please reply in news]

Ajay Garg (ajayz90@.hotmail.com) writes:
> I am running the following OSQL command and capturing the return code
> for the error .Whenver i have an error like server not exists or uable
> to login I get a return code of 1 for the %ERRORLEVEL%.However
> whenever I have an errorof a wrong dbcompatibility error the retun
> code id 1 even though sql returns an iformation message from OSQL that
> the right copatibilty levels are 60,70 and 80.How can i get OSQL to
> return the right return code whenver a error of this type occurrs from
> batch mode sql.The OSQL i am running from the batch is
> osql -S%SrvName% -U%Username% -P%Userpswd% -n -w 132 -d%DBname%
> -Q%sqlcmd% -o%Dirrpt%\%DBname%_%SPname%.txt
> ECHO %errorlevel% >> %logbatch%
> IF %ERRORLEVEL% NEQ 0 Goto SQLError
> sqlcms is exec sp_dbcompatibiltylevel srvrname, dbname 80

Here is a simple example that illustrates:

E:\temp>osql -E -n -Q "EXIT (SELECT 47)"

----
47

(1 row affected)

E:\temp>echo %ERRORLEVEL%
47

E:\temp
By putting the entire SQL batch within EXIT(), OSQL will return the
value of the last result set to the command-line environment.

For details, see the topic on OSQL in Books Online.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns9402EF532A497Yazorman@.127.0.0.1>...
> [posted and mailed, please reply in news]
> Ajay Garg (ajayz90@.hotmail.com) writes:
> > I am running the following OSQL command and capturing the return code
> > for the error .Whenver i have an error like server not exists or uable
> > to login I get a return code of 1 for the %ERRORLEVEL%.However
> > whenever I have an errorof a wrong dbcompatibility error the retun
> > code id 1 even though sql returns an iformation message from OSQL that
> > the right copatibilty levels are 60,70 and 80.How can i get OSQL to
> > return the right return code whenver a error of this type occurrs from
> > batch mode sql.The OSQL i am running from the batch is
> > osql -S%SrvName% -U%Username% -P%Userpswd% -n -w 132 -d%DBname%
> > -Q%sqlcmd% -o%Dirrpt%\%DBname%_%SPname%.txt
> > ECHO %errorlevel% >> %logbatch%
> > IF %ERRORLEVEL% NEQ 0 Goto SQLError
> > sqlcms is exec sp_dbcompatibiltylevel srvrname, dbname 80
> Here is a simple example that illustrates:
> E:\temp>osql -E -n -Q "EXIT (SELECT 47)"
> ----
> 47
> (1 row affected)
> E:\temp>echo %ERRORLEVEL%
> 47
> E:\temp>
> By putting the entire SQL batch within EXIT(), OSQL will return the
> value of the last result set to the command-line environment.
> For details, see the topic on OSQL in Books Online.

I noticed someting even more interesting.Even though the return code
was 0
when there was an error the job on sql server which actually failed
showed that it ran sucessfully.Even though when i run the job as an
Xp_cmdshell command on the sql server it shows that it failed what
could be the reason that it behaves that way?

Thanks in anticipation.

Ajay|||Ajay Garg (ajayz90@.hotmail.com) writes:
> I noticed someting even more interesting.Even though the return code
> was 0
> when there was an error the job on sql server which actually failed
> showed that it ran sucessfully.Even though when i run the job as an
> Xp_cmdshell command on the sql server it shows that it failed what
> could be the reason that it behaves that way?

I'm sorry, but I don't follow. Could you clarify with an example?

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp