INFO: INSERT/DELETE/UPDATE Behavior in SQL Server ODBC Applications (269357)


The information in this article applies to:

  • Microsoft ODBC Driver for SQL Server 3.5
This article was previously published under Q269357

SUMMARY

With the Microsoft ODBC Driver for SQL Server, the SQLExecDirect and SQLExecute functions return SQL_NO_DATA_FOUND when no rows are affected by an UPDATE, DELETE, or INSERT statement.

MORE INFORMATION

The following are examples of where an UPDATE, DELETE, or INSERT would not affect any rows: 
UPDATE pubs..publishers SET pub_id='1232' WHERE 1=2

INSERT INTO temptable (SELECT * FROM pubs..publishers WHERE 1=2)
In such cases, the return code for SQLExecDirect or SQLExecute would be SQL_NO_DATA_FOUND (instead of SQL_SUCCESS). This is in accordance with the ODBC specification.

This change was made in the Microsoft ODBC Driver for SQL Server version 3.50.0305 and later.

If this change affects your application, you can either add functionality in your code to process the SQL_NO_DATA_FOUND return code, or you can use the SET NOCOUNT ON statement so that the return code will be SQL_SUCCESS. The use of SET NOCOUNT ON tells the server not to return the number of rows that are affected by the UPDATE, DELETE, or INSERT statement; therefore, the ODBC driver just returns SQL_SUCCESS.
Modification Type:MajorLast Reviewed:8/23/2001
Keywords:kbDSupport kbinfo KB269357
©2002 Microsoft Corporation. All rights reserved.