Microsoft KB Archive/232991

= PRB: SQLExtendedFetch with SQL_FETCH_RELATIVE Fails to Return all Records after a Delete and Rollback using Keyset Cursors =

Article ID: 232991

Article Last Modified on 10/17/2003

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft ODBC Driver for Microsoft SQL Server 3.5
 * Microsoft ODBC Driver for Microsoft SQL Server 3.6
 * Microsoft ODBC Driver for Microsoft SQL Server 3.7

-



This article was previously published under Q232991



SYMPTOMS
SQLExtendedFetch, with SQL_FETCH_RELATIVE, may not return all the records after a Delete operation followed by a rollback transaction for a Keyset cursor.



CAUSE
This is due to the design of the cursor worktable to be in it's own private transaction. Commits and rollbacks on the main transaction do not affect the worktable, and the row is deleted from the worktable prior to the rollback.



RESOLUTION
The records are not deleted because of the rollback. The same will not occur with a dynamic cursor. Due to the nature of a dynamic cursor, the row will be seen when fetching relative after the rollback.



STATUS
This behavior is by design.



MORE INFORMATION
Microsoft SQL Server 7.0 causes all the serverside cursors, except static, to be closed on a rollback. However SQL Server 7.0 Service Pack 1 (SP1) was modified to retain all the cursors even on a rollback.

Steps to Reproduce Behavior
From ISQL create the following table with the index and trigger for a delete operation: if exists (select * from sysobjects where id = object_id(N'dbo.ADOTest_Delete') and OBJECTPROPERTY(id, N'IsTrigger') = 1) drop trigger dbo.ADOTest_Delete GO

if exists (select * from sysobjects where id = object_id(N'dbo.ADOTest') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table dbo.ADOTest GO

CREATE TABLE dbo.ADOTest (   fld_id smallint NULL,    fld_dscr varchar (50) NULL ,    fld_test smallint NULL ) GO

CREATE UNIQUE  INDEX ADOTest1 ON dbo.ADOTest(fld_id) GO

CREATE UNIQUE  INDEX ADOTest2 ON dbo.ADOTest(fld_dscr) GO

GRANT REFERENCES,  SELECT ,  INSERT ,  DELETE ,  UPDATE  ON dbo.ADOTest  TO guest GO

SET QUOTED_IDENTIFIER OFF    SET ANSI_NULLS  ON GO

CREATE TRIGGER ADOTest_Delete ON ADOTest FOR DELETE AS DECLARE @Fld_Test smallint Begin select @Fld_Test = Fld_Test from Deleted if @Fld_Test = 0 begin              /* If they're trying to delete a non-test entry, give them an error and rollback the transaction */ RaisError ("You can not delete Non-Test records, this delete will not be performed",11,-1) RollBack Transaction End end GO

SET QUOTED_IDENTIFIER OFF    SET ANSI_NULLS  ON GO

Insert into ADOTest values (1, "First Record",-1) Insert into ADOTest values (2, "Second Record",0) Insert into ADOTest values (3, "3rd Record",-1) Insert into ADOTest values (4, "4th Record",0)

Information from ODBC Test
FullConnect SQLSetConnectAttr: -- Set the SQL_COPT_SS_PRESERVE_CURSORS to ON In: ConnectionHandle = 0x00D42414, Attribute = SQL_COPT_SS_PRESERVE_CURSORS, ValuePtr = 1, StringLength = SQL_IS_INTEGER=-6, fAttribute Type = SQL_C_ULONG=-18 Return: SQL_SUCCESS=0 SQLSetConnectAttr: -- Set AUTO_COMMIT to OFF. In: ConnectionHandle = 0x00D42414, Attribute = Conn: SQL_ATTR_AUTOCOMMIT=102, ValuePtr = SQL_AUTOCOMMIT_OFF=0, StringLength = SQL_IS_INTEGER=-6, fAttribute Type = SQL_C_SLONG=-16 Return: SQL_SUCCESS=0

SQLSetStmtAttr: -- Set the rowset size for the cursor In: StatementHandle = 0x00D42AC4, Attribute = SQL_ROWSET_SIZE=9, ValuePtr = 100, StringLength = SQL_IS_INTEGER=-6, fAttribute Type = SQL_C_SLONG=-16 Return: SQL_SUCCESS=0 SQLSetStmtAttr: -- Set the cursor type to Keyset driven In: StatementHandle = 0x00D42AC4, Attribute = SQL_ATTR_CURSOR_TYPE=6, ValuePtr = SQL_CURSOR_KEYSET_DRIVEN=1, StringLength = SQL_IS_INTEGER=-6, fAttribute Type = SQL_C_SLONG=-16 Return: SQL_SUCCESS=0 SQLSetStmtAttr: -- Set the concurrency to Optimistic based on rowversioning In: StatementHandle = 0x00D42AC4, Attribute = SQL_ATTR_CONCURRENCY=7, ValuePtr = SQL_CONCUR_ROWVER=3, StringLength = SQL_IS_INTEGER=-6, fAttribute Type = SQL_C_SLONG=-16 Return: SQL_SUCCESS=0 SQLExecDirect: --Execute the select stmt that fetches the row. In: hstmt = 0x00D42AC4, szSqlStr = "select * from adotest order by fld_id", cbSqlStr = -3 Return: SQL_SUCCESS=0 Bind Col All: -- Bind all the columns to their resp datatypes. icol, fCType, cbValueMax, pcbValue, rgbValue 1, SQL_C_SSHORT=-15, 2, 0, 0 2, SQL_C_CHAR=1, 51, 0, "" 3, SQL_C_SSHORT=-15, 2, 0, 0 SQLExtendedFetch: -- Fetch the first set of records In: StatementHandle = 0x00D42AC4, FetchOrientation = SQL_FETCH_FIRST=2, FetchOffset = 1, RowCountPtr = 0x00157C70, RowStatusArray = 0x00157090 Return: SQL_SUCCESS=0 Out:   *RowCountPtr = 4, *RowStatusArray = 0 SQLSetPos: -- Do the delete on the cursor that causes a rollback and generate the error (due to the code in the trigger). In: StatementHandle = 0x00D42AC4, RowNumber = 2, Operation = SQL_DELETE=3, LockType = SQL_LOCK_NO_CHANGE=0 Return: SQL_ERROR=-1 stmt:  szSqlState = "37000", *pfNativeError = 50000, *pcbErrorMsg = 117 szErrorMsg = "[Microsoft][ODBC SQL Server Driver][SQL Server]You can not delete Non-Test records, this delete will not be performed" SQLExtendedFetch: -- Call with SQL_FETCH_RELATIVE. Record 2 is missing. In: StatementHandle = 0x00D42AC4, FetchOrientation = SQL_FETCH_RELATIVE=6, FetchOffset = 0, RowCountPtr = 0x00158A48, RowStatusArray = 0x00157090 Return: SQL_SUCCESS=0 Out:   *RowCountPtr = 4, *RowStatusArray = 0 The Delete operation causes a rollback in the trigger. SQLExtendedFetch is called with SQL_FETCH_RELATIVE with a 0 offset and record number 2 vanishes from the cursor.

