FreeTDS Development Group

["Lowden, James K" <LowdenJK AT bernstein.com>]

> From: ZIGLIO, Frediano, VF-IT
> Sent: Thursday, June 30, 2005 5:55 AM
> 
> Mmm... I renamed fields however I noted that some tests for msg_level
> (now severity) looks like tests to set priv_msg_type... perhaps
> it's better to use this variable instead to test severity? I noted
> this code in ODBC (I don't remember where it came...)
> 
> if (severity <= 10 && dbc && !TDS_IS_MSSQL(dbc->tds_socket) &&
> msg->sql_state && msg->sql_state[0]
>     && strncmp(msg->sql_state, "00", 2) != 0) {
>   if (strncmp(msg->sql_state, "01", 2) != 0 && strncmp(msg->sql_state,
> "IM", 2) != 0)
>     severity = 11;
> } if (severity <= 10) {
>   if (errs->lastrc == SQL_SUCCESS)
>     errs->lastrc = SQL_SUCCESS_WITH_INFO;
> } else {
>   errs->lastrc = SQL_ERROR;
> }
> 
> Perhaps priv_msg_type was intended for that purpose ??

Maybe.  We shouldn't use two varibles where one suffices, though, eh?
;-)

This topic can be very confusing.  First, there are messages from the
server and error messages from the library.  Each has a severity, but
their meanings differ.  Second, at least in db-lib, some messages from
the server *cause* an error message from the library.  

Consider sysmessages.  Both vendors say severity <= 16 are pibkac user
errors.  But the documentation is confusing.  Looking just at Microsoft
for a moment:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/
ac_8_con_05_4pv7.asp

" Errors 
        The errors from sysmessages with a severity of 11 or higher.
        Any RAISERROR statement with a severity of 11 or higher."

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql
/tr_errorformats_0tpv.asp

"Severity Level 10: Status Information

This is an informational message that indicates a problem caused by
mistakes in the information the user has entered. Severity level 0 is
not visible in SQL Server.

Severity Levels 11 through 16

These messages indicate errors that can be corrected by the user."

Looking at master..sysmessages:

1> select cast(Q as smallint) Q, m.severity, cast(description as
varchar(60)) from sysmessages m join (select min(error) minerr, max
(error) maxerr, count(*) Q, severity from sysmessages group by severity)
as A on m.error = A.minerr order by m.severity
2> go
 Q      severity
 ------ --------
------------------------------------------------------------
    167        0 CREATE SCHEMA failed due to previous errors.
    600       10 Version date of last upgrade: 10/11/90.
    145       11 Object ID %ld specified as a default for table ID %ld,
colum
      1       12 Could not continue scan with NOLOCK due to data
movement.
      8       13 Cannot issue SAVE TRANSACTION when there is no active
transa
     50       14 %ls permission denied on object '%.*ls', database
'%.*ls', o
    163       15 Incorrect syntax near '%.*ls'.
   2490       16 Too many table names in the query. The maximum
allowable is
     48       17 No more buffers can be stolen.
      7       18 The server encountered a stack overflow during compile
time.
      8       19 Could not bind foreign key constraint. Too many tables
invol
     71       20 Normalization error in node %ls.
     53       21 Error writing audit trace.  SQL Server is shutting
down.
      7       22 Could not find row in sysindexes for clustered index on
syst
      7       23 [Possible schema corruption. Run DBCC
CHECKCATALOG.|||||||||
      2       24 I/O error %ls detected during %S_MSG at offset
%#016I64x in
      4       25 >>> VOLUME SWITCH <<< (not for output!)

That explains your code snippet.  Severity 0 is  print message e.g.
15338 "The %s was renamed to '%s'."  Severity 10 is a warning. As the
db-lib docs say, 11-16 are user-correctable.  ODBC doesn't distinguish
between user-correctable and really bad server errors.  

So much for server messages.  

db-lib errors (like "Attempt to bind to a non-existent column.") also
have severity, but with different meanings, cf. the table at the end of
doc/dblib_errors.txt.  Some of these e.g. SYBETIME will be generated
from libtds.  When one reaches ODBC, it will have to correctly interpret
the severity level; ignoring severities < 10 (or treating them like
warnings) will not work.  

As you know, server messages and library error messages have different
handlers in db-lib (and are routed differently by libtds).  Server
message handlers cannot return an error code; that function belongs only
to the error handler.  

I hope that clears things up.  (!)

Regards, 

--jkl




-----------------------------------------
The information contained in this transmission may contain privileged and
confidential information and is intended only for the use of the person(s) 
named
above. If you are not the intended recipient, or an employee or agent 
responsible
for delivering this message to the intended recipient, any review, 
dissemination,
distribution or duplication of this communication is strictly prohibited. If 
you are
not the intended recipient, please contact the sender immediately by reply 
e-mail
and destroy all copies of the original message. Please note that we do not 
accept
account orders and/or instructions by e-mail, and therefore will not be 
responsible
for carrying out such orders and/or instructions.  If you, as the intended 
recipient
of this message, the purpose of which is to inform and update our clients, 
prospects
and consultants of developments relating to our services and products, would 
not
like to receive further e-mail correspondence from the sender, please "reply" 
to the
sender indicating your wishes.  In the U.S.: 1345 Avenue of the Americas, New 
York,
NY 10105.