I have two computers, a Windows XP machine with MDAC 2.7 SP1 (sqlsrv32.dll
version 2000.81.9042.0) and a Windows Server 2003 machine with MDAC 2.8 SP2
for Server 2003 (sqlsrv32.dll version 2000.86.1830.0) as confirmed by MDAC
Component Checker.

They are both connecting to the same machine with SQL Server 2000 with SP4.

I am submitting the several SQL commands through .NET OleDb (Framework 1.1)
to a JET DB (Access 2003) with a linked table to SQL Server. I have also
connected to JET through ODBC in the past as well with the same results.

I am inserting a record into a linked table called "Events" with one
autonumbered IDENTITY column called EventKey.

I have started a transaction through the BeginTransaction .NET methods. I
submit the following SQL commands (.NET pseudo-code'ish):

ExecuteNonQuery("INSERT INTO Events (EventMember) VALUES (6)")
var key = ExecuteScalar("SELECT @@IDENTITY")
ExecuteQuery("SELECT * FROM Events WHERE EventKey = " & key)


The first two commands run successfully, but the third command fails with
the following error: "System.Data.OleDb.OleDbException: ODBC--call failed."
after about a minute timeout.

Upon checking SQL Enterprise Manager, there are two processes (one blocking,
one blocked). The blocking process last executed the "SELECT @@IDENTITY",
the blocked process executed the "select * from..." query. After a lock
timeout, the SELECT * query returns with that error.

My question is: why are TWO connections being made to SQL Server for the
same transaction? Since all three queries are coming from the same source
JET connection, of course that second connection to SQL server while the
first connection has that inserted record locked will fail to read it.

This works perfectly from MDAC 2.7, but fails every time with MDAC 2.8. I
have tried to go through the "List of bug fixes" KB articles, but nothing was
listed about a fix that I would think may break this idea other than
asynchronous fetching?

Any ideas - our application heavily uses this approach to insert new records
through ADO.NET and then select the records back during the same transaction.


Thanks,

Mike Dearman
Lead Software Engineer

I used the ODBC trace feature to obtain the following log. It definitely
shows the ODBC driver disconnecting then reconnecting between the SELECT
@@IDENTIY and the SELECT * commands. The weird thing is that it does a
SQLExecDirectW, then a fetch, then a getdata that all appears to be
successful. Then it does another fetch with return code 100
(SQL_NO_DATA_FOUND). After that, it seems to free the statement, and proceed
to call SQLAllocConnect again.

Here is the complete ODBC trace log for the three SQL commands (cleaned up a
bit though - log too long):

----------------------------------


ENTER SQLAllocEnv
HENV * 0C0FE7B8

EXIT SQLAllocEnv with return code 0 (SQL_SUCCESS)
HENV * 0x0C0FE7B8 ( 0x0f881788)

ENTER SQLAllocConnect
HENV 0F881788
HDBC * 0C0FE8C8

EXIT SQLAllocConnect with return code 0 (SQL_SUCCESS)
HENV 0F881788
HDBC * 0x0C0FE8C8 ( 0x0f881830)

ENTER SQLSetConnectOption
HDBC 0F881830
SQLINTEGER 103 <SQL_LOGIN_TIMEOUT>
SQLPOINTER 0x00000014

EXIT SQLSetConnectOption with return code 0 (SQL_SUCCESS)
HDBC 0F881830
SQLINTEGER 103 <SQL_LOGIN_TIMEOUT>
SQLPOINTER 0x00000014 (BADMEM)

ENTER SQLSetConnectAttrW
SQLHDBC 0F881830
SQLINTEGER 30002 <unknown>
SQLPOINTER [Unknown attribute 30002]
SQLINTEGER -3

EXIT SQLSetConnectAttrW with return code 0 (SQL_SUCCESS)
SQLHDBC 0F881830
SQLINTEGER 30002 <unknown>
SQLPOINTER [Unknown attribute 30002]
SQLINTEGER -3

ENTER SQLDriverConnectW
HDBC 0F881830
HWND 00000000
WCHAR * 0x4BF78088 [ -3] "******\ 0"
SWORD -3
WCHAR * 0x4BF78088
SWORD 2
SWORD * 0x00000000
UWORD 0 <SQL_DRIVER_NOPROMPT>

EXIT SQLDriverConnectW with return code 1 (SQL_SUCCESS_WITH_INFO)
HDBC 0F881830
HWND 00000000
WCHAR * 0x4BF78088 [ -3] "******\ 0"
SWORD -3
WCHAR * 0x4BF78088
SWORD 2
SWORD * 0x00000000
UWORD 0 <SQL_DRIVER_NOPROMPT>

DIAG [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr
failed (0)
DIAG [01000] [Microsoft][ODBC SQL Server Driver][SQL Server]Changed
database context to 'UAA'. (5701)
DIAG [01000] [Microsoft][ODBC SQL Server Driver][SQL Server]Changed
language setting to us_english. (5703)

ENTER SQLGetInfoW
HDBC 0F881830
UWORD 9 <SQL_ODBC_API_CONFORMANCE>
PTR 0x0C0FE8EC
SWORD 2
SWORD * 0x0C0FE8E4

EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS)
HDBC 0F881830
UWORD 9 <SQL_ODBC_API_CONFORMANCE>
PTR 0x0C0FE8EC (2)
SWORD 2
SWORD * 0x0C0FE8E4 (2)

ENTER SQLGetInfoW
HDBC 0F881830
UWORD 6 <SQL_DRIVER_NAME>
PTR 0x0C0FE7FC
SWORD 200
SWORD * 0x0C0FE8E4

EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS)
HDBC 0F881830
UWORD 6 <SQL_DRIVER_NAME>
PTR 0x0C0FE7FC [ 24] "SQLSRV32.DLL"
SWORD 200
SWORD * 0x0C0FE8E4 (24)

ENTER SQLGetInfoW
HDBC 0F881830
UWORD 46 <SQL_TXN_CAPABLE>
PTR 0x0C0FE7C6
SWORD 2
SWORD * 0x0C0FE398

EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS)
HDBC 0F881830
UWORD 46 <SQL_TXN_CAPABLE>
PTR 0x0C0FE7C6 (2)
SWORD 2
SWORD * 0x0C0FE398 (2)

ENTER SQLGetInfoW
HDBC 0F881830
UWORD 23 <SQL_CURSOR_COMMIT_BEHAVIOR>
PTR 0x0E029928
SWORD 2
SWORD * 0x0C0FE398

EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS)
HDBC 0F881830
UWORD 23 <SQL_CURSOR_COMMIT_BEHAVIOR>
PTR 0x0E029928 (1)
SWORD 2
SWORD * 0x0C0FE398 (2)

ENTER SQLGetInfoW
HDBC 0F881830
UWORD 24 <SQL_CURSOR_ROLLBACK_BEHAVIOR>
PTR 0x0E02992A
SWORD 2
SWORD * 0x0C0FE398

EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS)
HDBC 0F881830
UWORD 24 <SQL_CURSOR_ROLLBACK_BEHAVIOR>
PTR 0x0E02992A (1)
SWORD 2
SWORD * 0x0C0FE398 (2)

ENTER SQLGetInfoW
HDBC 0F881830
UWORD 1 <SQL_ACTIVE_STATEMENTS>
PTR 0x0C0FE7CC
SWORD 2
SWORD * 0x0C0FE7B2

EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS)
HDBC 0F881830
UWORD 1 <SQL_ACTIVE_STATEMENTS>
PTR 0x0C0FE7CC (1)
SWORD 2
SWORD * 0x0C0FE7B2 (2)

ENTER SQLSetConnectOption
HDBC 0F881830
SQLINTEGER 101 <SQL_ACCESS_MODE>
SQLPOINTER 0x00000000

EXIT SQLSetConnectOption with return code 0 (SQL_SUCCESS)
HDBC 0F881830
SQLINTEGER 101 <SQL_ACCESS_MODE>
SQLPOINTER 0x00000000

ENTER SQLAllocStmt
HDBC 0F881830
HSTMT * 0C0FE390

EXIT SQLAllocStmt with return code 0 (SQL_SUCCESS)
HDBC 0F881830
HSTMT * 0x0C0FE390 ( 0x0f8820f0)

ENTER SQLGetStmtOption
HSTMT 0F8820F0
UWORD 0
PTR 0x0C0FE33C

EXIT SQLGetStmtOption with return code 0 (SQL_SUCCESS)
HSTMT 0F8820F0
UWORD 0
PTR 0x0C0FE33C

ENTER SQLSetStmtOption
HSTMT 0F8820F0
UWORD 0 <SQL_QUERY_TIMEOUT>
SQLPOINTER 0x0000003C

EXIT SQLSetStmtOption with return code 0 (SQL_SUCCESS)
HSTMT 0F8820F0
UWORD 0 <SQL_QUERY_TIMEOUT>
SQLPOINTER 0x0000003C (BADMEM)

ENTER SQLExecDirectW
HSTMT 0F8820F0
WCHAR * 0x1B074AA0 [ -3] "SELECT Config, nValue FROM
MSysConf\ 0"
SDWORD -3

EXIT SQLExecDirectW with return code -1 (SQL_ERROR)
HSTMT 0F8820F0