PRB: OSQL Error Message Leaves Out ODBC Header Information (305192)

SYMPTOMS

ODBC header information is not included in the error message returned by the OSQL utility. This information is excluded for errors returned by either the SQL Server ODBC Driver or SQL Server.

For example, the following command

osql -E -STHISSERVER -Q"select * from ThisObjectDoesNotExist"

returns:

Msg 208, Level 16, State 1, Server THISSERVER, Procedure , Line 1
Invalid object name 'ThisObjectDoesNotExist'.

An earlier version of OSQL will return:

Msg 208, Level 16, State 1, Server THISSERVER, Procedure , Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]
Invalid object name 'ThisObjectDoesNotExist'.

CAUSE

The output of the OSQL application has changed with SQL Server 2000. Users are not expected to rely on the error strings returned, which may change at any time. It is more reliable to check for error codes.

WORKAROUND

Add the -b and -r options to the OSQL command, and the returning program should check the DOS ERRORLEVEL. For example, a batch file will execute the following OSQL command and display the ERRORLEVEL using:

osql -E -STHISSERVER -Q"select * from ThisObjectDoesNotExist" -dpubs -b -r1
echo %ERRORLEVEL%

A value of 1 indicates that an error was returned by OSQL.