BUG: DBSQLEXEC() Returns Fail When RAISERROR Is Called

  - Microsoft SQL Server Programmer's Toolkit, version 4.21

SYMPTOMS

The Windows 3.x DB-Library functions, dbsqlexec() or dbsqlok(), return fail immediately following a RAISERROR event. The problem is also seen in VBSQL applications when calling the equivalent functions sqlexec% and sqlok%. This problem does not happen with DB-Library for Windows NT.

However, if the FAIL is ignored, the application can continue with the processing of any pending results from the other queries.

CAUSE

If a RAISERROR is the first thing called in a stored procedure or SQL batch, the dberrhandler will be called before dbsqlexec() or dbsqlok() return and after the error handler has finished a FAIL is returned to the function.

WORKAROUND

Three different actions can be taken to avoid the problem:

1. Use a global semaphore; set it to true in the dberrhandler if a RAISERROR occurs, then if dbsqlexec() returns fail, you can check to see if the semaphore has been set. Make sure the logic of your program will unset the semaphore appropriately.

2. Use the PRINT statement instead of RAISERROR. The RAISERRROR statement was designed to notify the client application of an error event; posting a RAISERROR as the first part of a batch or stored procedure does not typically indicate that an error has occurred. If the sole purpose is to send a message to the client, the PRINT command will meet the requirement.

3. Avoid using RAISERROR statements of a SQL batch or stored procedure. As long as RAISERROR is not the first event to return results to the client, the problem will not occur.

STATUS

Microsoft has confirmed this to be a problem in DB-Library version 4.21.00 for Windows 3.x. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.