Microsoft KB Archive/293901

= BUG: Incorrect Number of Rows Inserted Using MS ODBC Driver and Stored Procedure on SQL Server =

Article ID: 293901

Article Last Modified on 12/5/2003

-

APPLIES TO


 * Microsoft ODBC Driver for Microsoft SQL Server 3.7
 * Microsoft ODBC Driver for Microsoft SQL Server 3.7
 * Microsoft Data Access Components 2.0
 * Microsoft Data Access Components 2.1
 * Microsoft Data Access Components 2.5
 * Microsoft Data Access Components 2.6
 * Microsoft Data Access Components 2.7

-



This article was previously published under Q293901



SYMPTOMS
If you use the Microsoft ODBC Driver for SQL Server to execute a stored procedure that inserts multiple rows into a SQL Server table, an inconsistent number of records may be inserted.

This problem can occur when all of the following conditions are met:
 * You are using the Microsoft ODBC Driver to connect to SQL Server.
 * The stored procedure that inserts records into the table does not specify SET NOCOUNT ON.
 * You do not call SQLMoreResults to extract all tokens from SQL Server.
 * You use SQLSetEnvAttr to programmatically set ODBC versions to SQL_OV_ODBC3 instead of SQL_OV_ODBC2.
 * SQL_ATTR_AUTOCOMMIT is set ON.



CAUSE
When you do not specify SET NOCOUNT ON in your stored procedure, SQL Server returns a token for every INSERT statement, and if you do not extract all the returned tokens by using SQLMoreResults, you will get inconsistent results.



RESOLUTION
You can work around this problem in the following ways:
 * Specify SET NOCOUNT ON in the stored procedure.
 * Call SQLMoreResults until you get SQL_NO_DATA to extract all tokens from SQL Server.



STATUS
Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article.



Steps to Reproduce Behavior
  Create a table &quot;test&quot; by using the following code: if exists (select * from sysobjects where id = object_id(N'[dbo].[test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[test] GO

CREATE TABLE [dbo].[test] (   [theId] [int] NOT NULL,    [name] [char] (50) NULL ) ON [PRIMARY] GO   Create a stored procedure &quot;TestSp&quot; by using the following SQL code: CREATE PROCEDURE TestSP AS --SET NOCOUNT ON BEGIN DECLARE @Number INT

Select @Number = 1 WHILE (@Number <= 800) BEGIN

-- Create new row in table test here.

INSERT INTO test( theId,name)VALUES (@Number,'Testing')

-- Increment Sequence number. SELECT @Number=@Number + 1 END END   Create a Win32 console application and add the following code:
 * 1) include 
 * 2) include 
 * 3) include 

void main(void) {   HENV henv; HDBC hdbc; HSTMT hstmt; SQLRETURN retcode;

retcode = SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&henv);

// We need to set the ODBC version to SQL_OV_ODBC3. retcode = SQLSetEnvAttr(henv,SQL_ATTR_ODBC_VERSION,(void *)SQL_OV_ODBC3,SQL_C_SLONG) ;

retcode = SQLAllocHandle(SQL_HANDLE_DBC,henv, &hdbc);

//Make sure that autocommit is set to ON. retcode = SQLSetConnectAttr(hdbc,SQL_ATTR_AUTOCOMMIT,(void *)SQL_AUTOCOMMIT_ON,0); //Change the DSN, user ID, and password accordingly. retcode = SQLConnect(hdbc, (SQLCHAR*)&quot;DSN NAME&quot;,SQL_NTS, (SQLCHAR*) &quot;USER ID&quot;,SQL_NTS ,(SQLCHAR*)&quot;PASSWORD&quot;,SQL_NTS); retcode = SQLAllocHandle(SQL_HANDLE_STMT,hdbc, &hstmt);

retcode = SQLPrepare(hstmt,(unsigned char *)&quot;{call TestSP}&quot;,SQL_NTS);

retcode = SQLExecute(hstmt); //Uncomment the following SQLMoreResults call to collect all returned tokens. //while (retcode != SQL_NO_DATA) //retcode = SQLMoreResults(hstmt); retcode = SQLFreeHandle(SQL_HANDLE_STMT, hstmt); retcode = SQLDisconnect(hdbc); retcode = SQLFreeHandle(SQL_HANDLE_DBC, hdbc); retcode = SQLFreeHandle(SQL_HANDLE_ENV, henv); }

</li> In the code, change the data source name (DSN), user ID, and password.</li> Compile the project and run it.</li> Check the number of records that were inserted in table test.</li> Uncomment the while loop in your code, and then recompile and run the code. Note that the records entered in the table are accurate.</li> Comment the while loop or uncomment SET NOCOUNT ON in the stored procedure and then run the code. Note that the records entered are correct.</li></ol>

Additional query words: ODBC SQLSRV

Keywords: kbbug kbnofix kbdriver KB293901

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.