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

[Ajay Garg]

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%.Ho wever
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%\%DBna me%_%SPname%.tx t
ECHO %errorlevel% >> %logbatch%
IF %ERRORLEVEL% NEQ 0 Goto SQLError

sqlcms is exec sp_dbcompatibil tylevel srvrname, dbname 80

Thanks in anticipation.

Ajay

[Erland Sommarskog]

[posted and mailed, please reply in news]

Ajay Garg (aj*****@hotmai l.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%.Ho wever
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%\%DBna me%_%SPname%.tx t
ECHO %errorlevel% >> %logbatch%
IF %ERRORLEVEL% NEQ 0 Goto SQLError

sqlcms is exec sp_dbcompatibil tylevel 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, so****@algonet. se

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

[Ajay Garg]

Erland Sommarskog <so****@algonet .se> wrote in message news:<Xn******* *************** @127.0.0.1>...

[posted and mailed, please reply in news]

Ajay Garg (aj*****@hotmai l.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%.Ho wever
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%\%DBna me%_%SPname%.tx t
ECHO %errorlevel% >> %logbatch%
IF %ERRORLEVEL% NEQ 0 Goto SQLError

sqlcms is exec sp_dbcompatibil tylevel 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.Eve n though the return code
was 0
when there was an error the job on sql server which actually failed
showed that it ran sucessfully.Eve n 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

[Erland Sommarskog]

Ajay Garg (aj*****@hotmai l.com) writes:

I noticed someting even more interesting.Eve n though the return code
was 0
when there was an error the job on sql server which actually failed
showed that it ran sucessfully.Eve n 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, so****@algonet. se

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