SQLSTATE Messages

SQLSTATE codes return values that are error, warning, or "no data found" messages. Here's how SQLSTATE and SQLCODE messages correspond to each other.

SQLSTATE code	SQLCODE code
First digit greater than 0	Negative number
First digit equal to 1	1
First digit equal to 2	100

If a WHENEVER SQLERROR CALL myproc() statement is in effect, myproc() is called when SQLSTATE returns a value with a nonzero first digit.

Use SQLSTATE to check for exceptions and warnings. To use SQLSTATE in a program, you must explicitly declare SQLSTATE within a host variable declaration block, for example:

EXEC SQL BEGIN DECLARE SECTION ;
char SQLSTATE [6] ;  // 5 characters for code and one character for
                // null terminator
EXEC SQL END DECLARE SECTION;

Errors caught at compile time by using the /SQLACCESS option will not be mapped to SQLSTATE values at run time.

Microsoft® SQL Server™ 2000 errors map to these SQLSTATE codes.

SQLSTATE code	SQL Server message or DB-Library error numbers	Description
01001	532,10095 (DB-Library)	Cursor update or delete failure due to optimistic concurrency check failure.
01003	8153	Elimination of null values by set operator (warning).
01004	n/a	Select or fetch into host variable that is too short.
02000	n/a	No data found. Equivalent to SQLCODE = 100. Occurs when: Cursor fetch at end of result set. Cursor fetch on empty result set. Singleton select returns no data. Searched delete affects no rows.* Insert/select affects no rows.* Searched update affects no rows.*  * These conditions are only detected when the program is compiled with the NOSQLACCESS option to NSQLPREP.
07002	n/a	Number of columns does not match number of host variables.
21000	512	Singleton select returns more than one row, or subquery preceded by = returns more than one value.
22001	8152	String data truncated (on right) on insert or update.
22002	n/a	Null value returned with no indicator variable provided.
22003	168,220,232,234, 236-238,244,246,248, 519-524,535,8115, 10015 (DB-Library)	Arithmetic overflow error.
22005	206,235,245,247,249, 256,257,305,409,518, 529	Data type conversion error.
22008	210,211,241,242,295, 296,517	Data type conversion error.
22012	8134	Divide by zero error.
22019	1010, 506	Invalid escape character.
22022	n/a	Indicator variable overflow.
22024	n/a	Unterminated C string passed as input host variable.
22025	310	Invalid escape sequence, for example, escape char not followed by %, _, or escape char.
23000	233,272,273,515,530, 547,1505,1508,2601, 2615,2626,2627,3604, 3605	Constraint violation.
24000	n/a	Invalid cursor state.
25000	266,277,611,628,3902, 3903,3906,3908,6401	Transaction state error.
40001	1205,1211,2625, 3309,7112	Transaction rollback due to deadlock.
42000	207,208,213,229, 230,260	Syntax error, permission violation, or other nonspecific error.
44000	550	View with check option violation.