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 wa
s
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 EngineerI 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 procee
d
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 SQLSetCon
nectAttr
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
WCHAR * 0x1B074AA0 [ -3] "SELECT Config, nValue FROM
MSysConf\ 0"
SDWORD -3
DIAG [S0002] [Microsoft][ODBC SQL Server Driver][SQL Server]
Invalid object
name 'MSysConf'. (208)
ENTER SQLErrorW
HENV 0F881788
HDBC 0F881830
HSTMT 0F8820F0
WCHAR * 0x0C0FE2D0 (NYI)
SDWORD * 0x0C0FE31C
WCHAR * 0x0DFD0048
SWORD 4095
SWORD * 0x0C0FE308
EXIT SQLErrorW with return code 0 (SQL_SUCCESS)
HENV 0F881788
HDBC 0F881830
HSTMT 0F8820F0
WCHAR * 0x0C0FE2D0 (NYI)
SDWORD * 0x0C0FE31C (208)
WCHAR * 0x0DFD0048 [ 78] "[Microsoft][ODBC SQL
Server
Driver][SQL Server]Invalid object name 'MSysConf'."
SWORD 4095
SWORD * 0x0C0FE308 (78)
ENTER SQLErrorW
HENV 0F881788
HDBC 0F881830
HSTMT 0F8820F0
WCHAR * 0x0C0FE2D0 (NYI)
SDWORD * 0x0C0FE31C
WCHAR * 0x0DFD00FA
SWORD 4006
SWORD * 0x0C0FE308
EXIT SQLErrorW with return code 100 (SQL_NO_DATA_FOUND)
HENV 0F881788
HDBC 0F881830
HSTMT 0F8820F0
WCHAR * 0x0C0FE2D0 (NYI)
SDWORD * 0x0C0FE31C
WCHAR * 0x0DFD00FA
SWORD 4006
SWORD * 0x0C0FE308
ENTER SQLFreeStmt
HSTMT 0F8820F0
UWORD 1 <SQL_DROP>
EXIT SQLFreeStmt with return code 0 (SQL_SUCCESS)
HSTMT 0F8820F0
UWORD 1 <SQL_DROP>
ENTER SQLGetInfoW
HDBC 0F881830
UWORD 17 <SQL_DBMS_NAME>
PTR 0x0C0FDFC4
SWORD 200
SWORD * 0x0C0FE08E
EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS)
HDBC 0F881830
UWORD 17 <SQL_DBMS_NAME>
PTR 0x0C0FDFC4 [ 40] "Microsoft SQL Server"
SWORD 200
SWORD * 0x0C0FE08E (40)
ENTER SQLGetInfoW
HDBC 0F881830
UWORD 0 <SQL_ACTIVE_CONNECTIONS>
PTR 0x0C0FE0D2
SWORD 2
SWORD * 0x0C0FE0E0
EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS)
HDBC 0F881830
UWORD 0 <SQL_ACTIVE_CONNECTIONS>
PTR 0x0C0FE0D2 (0)
SWORD 2
SWORD * 0x0C0FE0E0 (2)
ENTER SQLGetInfoW
HDBC 0F881830
UWORD 25 <SQL_DATA_SOURCE_READ_ONLY>
PTR 0x0C0FE0B8
SWORD 20
SWORD * 0x0C0FE0E0
EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS)
HDBC 0F881830
UWORD 25 <SQL_DATA_SOURCE_READ_ONLY>
PTR 0x0C0FE0B8 [ 2] "N"
SWORD 20
SWORD * 0x0C0FE0E0 (2)
ENTER SQLGetInfoW
HDBC 0F881830
UWORD 46 <SQL_TXN_CAPABLE>
PTR 0x0C0FE0D0
SWORD 2
SWORD * 0x0C0FE0A0
EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS)
HDBC 0F881830
UWORD 46 <SQL_TXN_CAPABLE>
PTR 0x0C0FE0D0 (2)
SWORD 2
SWORD * 0x0C0FE0A0 (2)
ENTER SQLGetInfoW
HDBC 0F881830
UWORD 23 <SQL_CURSOR_COMMIT_BEHAVIOR>
PTR 0x0C0FE0CC
SWORD 2
SWORD * 0x0C0FE0A0
EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS)
HDBC 0F881830
UWORD 23 <SQL_CURSOR_COMMIT_BEHAVIOR>
PTR 0x0C0FE0CC (1)
SWORD 2
SWORD * 0x0C0FE0A0 (2)
ENTER SQLGetInfoW
HDBC 0F881830
UWORD 24 <SQL_CURSOR_ROLLBACK_BEHAVIOR>
PTR 0x0C0FE0CE
SWORD 2
SWORD * 0x0C0FE0A0
EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS)
HDBC 0F881830
UWORD 24 <SQL_CURSOR_ROLLBACK_BEHAVIOR>
PTR 0x0C0FE0CE (1)
SWORD 2
SWORD * 0x0C0FE0A0 (2)
ENTER SQLGetInfoW
HDBC 0F881830
UWORD 29 <SQL_IDENTIFIER_QUOTE_CHAR>
PTR 0x0C0FE0B8
SWORD 20
SWORD * 0x0C0FE0E0
EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS)
HDBC 0F881830
UWORD 29 <SQL_IDENTIFIER_QUOTE_CHAR>
PTR 0x0C0FE0B8 [ 2] """
SWORD 20
SWORD * 0x0C0FE0E0 (2)
ENTER SQLSetConnectOption
HDBC 0F881830
SQLINTEGER 102 <SQL_AUTOCOMMIT>
SQLPOINTER 0x00000000
EXIT SQLSetConnectOption with return code 0 (SQL_SUCCESS)
HDBC 0F881830
SQLINTEGER 102 <SQL_AUTOCOMMIT>
SQLPOINTER 0x00000000
ENTER SQLAllocStmt
HDBC 0F881830
HSTMT * 0C0FEBD8
EXIT SQLAllocStmt with return code 0 (SQL_SUCCESS)
HDBC 0F881830
HSTMT * 0x0C0FEBD8 ( 0x0f8820f0)
ENTER SQLGetStmtOption
HSTMT 0F8820F0
UWORD 0
PTR 0x0C0FE718
EXIT SQLGetStmtOption with return code 0 (SQL_SUCCESS)
HSTMT 0F8820F0
UWORD 0
PTR 0x0C0FE718
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 SQLBindParameter
HSTMT 0F8820F0
UWORD 1
SWORD 1 <SQL_PARAM_INPUT>
SWORD 4 <SQL_C_LONG>
SWORD 4 <SQL_INTEGER>
SQLULEN 10
SWORD 0
PTR 0x2DFD41A4
SQLLEN 0
SQLLEN * 0x2DFD41A0
EXIT SQLBindParameter with return code 0 (SQL_SUCCESS)
HSTMT 0F8820F0
UWORD 1
SWORD 1 <SQL_PARAM_INPUT>
SWORD 4 <SQL_C_LONG>
SWORD 4 <SQL_INTEGER>
SQLULEN 10
SWORD 0
PTR 0x2DFD41A4
SQLLEN 0
SQLLEN * 0x2DFD41A0 (4)
ENTER SQLExecDirectW
HSTMT 0F8820F0
WCHAR * 0x2DFD61A8 [ -3] "INSERT INTO "dbo"."Events"
("EventMember") VALUES (?)\ 0"
SDWORD -3
EXIT SQLExecDirectW with return code 0 (SQL_SUCCESS)
HSTMT 0F8820F0
WCHAR * 0x2DFD61A8 [ -3] "INSERT INTO "dbo"."Events"
("EventMember") VALUES (?)\ 0"
SDWORD -3
ENTER SQLFreeStmt
HSTMT 0F8820F0
UWORD 3 <SQL_RESET_PARAMS>
EXIT SQLFreeStmt with return code 0 (SQL_SUCCESS)
HSTMT 0F8820F0
UWORD 3 <SQL_RESET_PARAMS>
ENTER SQLRowCount
HSTMT 0F8820F0
SQLLEN * 0x0C0FEBAC
EXIT SQLRowCount with return code 0 (SQL_SUCCESS)
HSTMT 0F8820F0
SQLLEN * 0x0C0FEBAC (1)
ENTER SQLFreeStmt
HSTMT 0F8820F0
UWORD 1 <SQL_DROP>
EXIT SQLFreeStmt with return code 0 (SQL_SUCCESS)
HSTMT 0F8820F0
UWORD 1 <SQL_DROP>
ENTER SQLAllocStmt
HDBC 0F881830
HSTMT * 0C0FE744
EXIT SQLAllocStmt with return code 0 (SQL_SUCCESS)
HDBC 0F881830
HSTMT * 0x0C0FE744 ( 0x0f8820f0)
ENTER SQLGetStmtOption
HSTMT 0F8820F0
UWORD 0
PTR 0x0C0FE6EC
EXIT SQLGetStmtOption with return code 0 (SQL_SUCCESS)
HSTMT 0F8820F0
UWORD 0
PTR 0x0C0FE6EC
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 * 0x1B075CE0 [ -3] "SELECT @.@.IDENTITY\ 0"
SDWORD -3
EXIT SQLExecDirectW with return code 0 (SQL_SUCCESS)
HSTMT 0F8820F0
WCHAR * 0x1B075CE0 [ -3] "SELECT @.@.IDENTITY\ 0"
SDWORD -3
ENTER SQLFetch
HSTMT 0F8820F0
EXIT SQLFetch with return code 0 (SQL_SUCCESS)
HSTMT 0F8820F0
ENTER SQLGetData
HSTMT 0F8820F0
UWORD 1
SWORD 4 <SQL_C_LONG>
PTR <unknown type>
SQLLEN 4
SQLLEN * 0x0C0FE738
EXIT SQLGetData with return code 0 (SQL_SUCCESS)
HSTMT 0F8820F0
UWORD 1
SWORD 4 <SQL_C_LONG>
PTR <unknown type>
SQLLEN 4
SQLLEN * 0x0C0FE738 (4)
ENTER SQLFetch
HSTMT 0F8820F0
EXIT SQLFetch with return code 100 (SQL_NO_DATA_FOUND)
HSTMT 0F8820F0
ENTER SQLFreeStmt
HSTMT 0F8820F0
UWORD 0 <SQL_CLOSE>
EXIT SQLFreeStmt with return code 0 (SQL_SUCCESS)
HSTMT 0F8820F0
UWORD 0 <SQL_CLOSE>
ENTER SQLFreeStmt
HSTMT 0F8820F0
UWORD 1 <SQL_DROP>
EXIT SQLFreeStmt with return code 0 (SQL_SUCCESS)
HSTMT 0F8820F0
UWORD 1 <SQL_DROP>
ENTER SQLAllocConnect
HENV 0F881788
HDBC * 0C0FEBF0
EXIT SQLAllocConnect with return code 0 (SQL_SUCCESS)
HENV 0F881788
HDBC * 0x0C0FEBF0 ( 0x0f882af0)
ENTER SQLSetConnectOption
HDBC 0F882AF0
SQLINTEGER 103 <SQL_LOGIN_TIMEOUT>
SQLPOINTER 0x00000014
EXIT SQLSetConnectOption with return code 0 (SQL_SUCCESS)
HDBC 0F882AF0
SQLINTEGER 103 <SQL_LOGIN_TIMEOUT>
SQLPOINTER 0x00000014 (BADMEM)
ENTER SQLSetConnectAttrW
SQLHDBC 0F882AF0
SQLINTEGER 30002 <unknown>
SQLPOINTER [Unknown attribute 30002]
SQLINTEGER -3
EXIT SQLSetConnectAttrW with return code 0 (SQL_SUCCESS)
SQLHDBC 0F882AF0
SQLINTEGER 30002 <unknown>
SQLPOINTER [Unknown attribute 30002]
SQLINTEGER -3
ENTER SQLDriverConnectW
HDBC 0F882AF0
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 0F882AF0
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 SQLSetCon
nectAttr
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 0F882AF0
UWORD 9 <SQL_ODBC_API_CONFORMANCE>
PTR 0x0C0FEC14
SWORD 2
SWORD * 0x0C0FEC0C
EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS)
HDBC 0F882AF0
UWORD 9 <SQL_ODBC_API_CONFORMANCE>
PTR 0x0C0FEC14 (2)
SWORD 2
SWORD * 0x0C0FEC0C (2)
ENTER SQLGetInfoW
HDBC 0F882AF0
UWORD 6 <SQL_DRIVER_NAME>
PTR 0x0C0FEB24
SWORD 200
SWORD * 0x0C0FEC0C
EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS)
HDBC 0F882AF0
UWORD 6 <SQL_DRIVER_NAME>
PTR 0x0C0FEB24 [ 24] "SQLSRV32.DLL"
SWORD 200
SWORD * 0x0C0FEC0C (24)
ENTER SQLGetInfoW
HDBC 0F882AF0
UWORD 46 <SQL_TXN_CAPABLE>
PTR 0x0C0FEAEE
SWORD 2
SWORD * 0x0C0FE6C0
EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS)
HDBC 0F882AF0
UWORD 46 <SQL_TXN_CAPABLE>
PTR 0x0C0FEAEE (2)
SWORD 2
SWORD * 0x0C0FE6C0 (2)
ENTER SQLGetInfoW
HDBC 0F882AF0
UWORD 23 <SQL_CURSOR_COMMIT_BEHAVIOR>
PTR 0x0E029C58
SWORD 2
SWORD * 0x0C0FE6C0
EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS)
HDBC 0F882AF0
UWORD 23 <SQL_CURSOR_COMMIT_BEHAVIOR>
PTR 0x0E029C58 (1)
SWORD 2
SWORD * 0x0C0FE6C0 (2)
ENTER SQLGetInfoW
HDBC 0F882AF0
UWORD 24 <SQL_CURSOR_ROLLBACK_BEHAVIOR>
PTR 0x0E029C5A
SWORD 2
SWORD * 0x0C0FE6C0
EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS)
HDBC 0F882AF0
UWORD 24 <SQL_CURSOR_ROLLBACK_BEHAVIOR>
PTR 0x0E029C5A (1)
SWORD 2
SWORD * 0x0C0FE6C0 (2)
ENTER SQLGetInfoW
HDBC 0F882AF0
UWORD 1 <SQL_ACTIVE_STATEMENTS>
PTR 0x0C0FEAF4
SWORD 2
SWORD * 0x0C0FEADA
EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS)
HDBC 0F882AF0
UWORD 1 <SQL_ACTIVE_STATEMENTS>
PTR 0x0C0FEAF4 (1)
SWORD 2
SWORD * 0x0C0FEADA (2)
ENTER SQLSetConnectOption
HDBC 0F882AF0
SQLINTEGER 101 <SQL_ACCESS_MODE>
SQLPOINTER 0x00000001
EXIT SQLSetConnectOption with return code 0 (SQL_SUCCESS)
HDBC 0F882AF0
SQLINTEGER 101 <SQL_ACCESS_MODE>
SQLPOINTER 0x00000001 (BADMEM)
ENTER SQLGetInfoW
HDBC 0F882AF0
UWORD 17 <SQL_DBMS_NAME>
PTR 0x0C0FEB58
SWORD 200
SWORD * 0x0C0FEC22
EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS)
HDBC 0F882AF0
UWORD 17 <SQL_DBMS_NAME>
PTR 0x0C0FEB58 [ 40] "Microsoft SQL Server"
SWORD 200
SWORD * 0x0C0FEC22 (40)
ENTER SQLSetConnectOption
HDBC 0F882AF0
SQLINTEGER 101 <SQL_ACCESS_MODE>
SQLPOINTER 0x00000001
EXIT SQLSetConnectOption with return code 0 (SQL_SUCCESS)
HDBC 0F882AF0
SQLINTEGER 101 <SQL_ACCESS_MODE>
SQLPOINTER 0x00000001 (BADMEM)
ENTER SQLSetConnectOption
HDBC 0F882AF0
SQLINTEGER 102 <SQL_AUTOCOMMIT>
SQLPOINTER 0x00000000
EXIT SQLSetConnectOption with return code 0 (SQL_SUCCESS)
HDBC 0F882AF0
SQLINTEGER 102 <SQL_AUTOCOMMIT>
SQLPOINTER 0x00000000
ENTER SQLAllocStmt
HDBC 0F882AF0
HSTMT * 320D2174
EXIT SQLAllocStmt with return code 0 (SQL_SUCCESS)
HDBC 0F882AF0
HSTMT * 0x320D2174 ( 0x0f8820f0)
ENTER SQLGetStmtOption
HSTMT 0F8820F0
UWORD 0
PTR 0x0C0FEB7C
EXIT SQLGetStmtOption with return code 0 (SQL_SUCCESS)
HSTMT 0F8820F0
UWORD 0
PTR 0x0C0FEB7C
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 SQLGetStmtOption
HSTMT 0F8820F0
UWORD 3
PTR 0x0C0FEB7C
EXIT SQLGetStmtOption with return code 0 (SQL_SUCCESS)
HSTMT 0F8820F0
UWORD 3
PTR 0x0C0FEB7C
ENTER SQLSetStmtOption
HSTMT 0F8820F0
UWORD 3 <SQL_MAX_LENGTH>
SQLPOINTER 0x7FFFFFFF
EXIT SQLSetStmtOption with return code 0 (SQL_SUCCESS)
HSTMT 0F8820F0
UWORD 3 <SQL_MAX_LENGTH>
SQLPOINTER 0x7FFFFFFF (BADMEM)
ENTER SQLExecDirectW
HSTMT 0F8820F0
WCHAR * 0x320D2690 [ -3] "SELECT "EventKey" ,"EventMemb
er"
,"EventDateFrom" ,"EventDateTo" ,"EventType" ,"EventName" ,"EventDetails"
,"EventAttendanceHere" ,"EventAttendanceOut" ,"EventAttendanceNight"
,"EventCost" ,"EventCostHere" ,"EventCostOut" ,"EventRevenue"
,"EventRevenueIn" ,"EventColIn" ,"EventFamilies" ,"EventContactHrs"
,"EventMeals" ,"EventOtherOrg1" ,"EventOtherOrg2" ,"EventOtherOrg3"
,"EventOtherOrg4" ,"EventOtherOrg5" ,"EventVolunteers" ,"EventVolHrs"
,"EventOutcomeMeasure" ,"EventOutcomeWeight" ,"EventOutcomeNarrative" FROM
"dbo"."Events" WHERE ("EventKey" = 591 ) \ 0"
SDWORD -3
EXIT SQLExecDirectW with return code -1 (SQL_ERROR)
HSTMT 0F8820F0
WCHAR * 0x320D2690 [ -3] "SELECT "EventKey" ,"EventMemb
er"
,"EventDateFrom" ,"EventDateTo" ,"EventType" ,"EventName" ,"EventDetails"
,"EventAttendanceHere" ,"EventAttendanceOut" ,"EventAttendanceNight"
,"EventCost" ,"EventCostHere" ,"EventCostOut" ,"EventRevenue"
,"EventRevenueIn" ,"EventColIn" ,"EventFamilies" ,"EventContactHrs"
,"EventMeals" ,"EventOtherOrg1" ,"EventOtherOrg2" ,"EventOtherOrg3"
,"EventOtherOrg4" ,"EventOtherOrg5" ,"EventVolunteers" ,"EventVolHrs"
,"EventOutcomeMeasure" ,"EventOutcomeWeight" ,"EventOutcomeNarrative" FROM
"dbo"."Events" WHERE ("EventKey" = 591 ) \ 0"
SDWORD -3
DIAG [S1T00] [Microsoft][ODBC SQL Server Driver]Timeout expired
(0)
ENTER SQLErrorW
HENV 0F881788
HDBC 0F882AF0
HSTMT 0F8820F0
WCHAR * 0x0C0FEAE8 (NYI)
SDWORD * 0x0C0FEB34
WCHAR * 0x0DFD0048
SWORD 4095
SWORD * 0x0C0FEB20
EXIT SQLErrorW with return code 0 (SQL_SUCCESS)
HENV 0F881788
HDBC 0F882AF0
HSTMT 0F8820F0
WCHAR * 0x0C0FEAE8 (NYI)
SDWORD * 0x0C0FEB34 (0)
WCHAR * 0x0DFD0048 [ 50] "[Microsoft][ODBC SQL
Server
Driver]Timeout expired"
SWORD 4095
SWORD * 0x0C0FEB20 (50)
ENTER SQLErrorW
HENV 0F881788
HDBC 0F882AF0
HSTMT 0F8820F0
WCHAR * 0x0C0FEAE8 (NYI)
SDWORD * 0x0C0FEB34
WCHAR * 0x0DFD00BE
SWORD 4036
SWORD * 0x0C0FEB20
EXIT SQLErrorW with return code 100 (SQL_NO_DATA_FOUND)
HENV 0F881788
HDBC 0F882AF0
HSTMT 0F8820F0
WCHAR * 0x0C0FEAE8 (NYI)
SDWORD * 0x0C0FEB34
WCHAR * 0x0DFD00BE
SWORD 4036
SWORD * 0x0C0FEB20
ENTER SQLFreeStmt
HSTMT 0F8820F0
UWORD 1 <SQL_DROP>
EXIT SQLFreeStmt with return code 0 (SQL_SUCCESS)
HSTMT 0F8820F0
UWORD 1 <SQL_DROP>
ENTER SQLTransact
HENV 0F881788
HDBC 0F882AF0
UWORD 1 <SQL_ROLLBACK>
EXIT SQLTransact with return code 0 (SQL_SUCCESS)
HENV 0F881788
HDBC 0F882AF0
UWORD 1 <SQL_ROLLBACK>
ENTER SQLSetConnectOption
HDBC 0F882AF0
SQLINTEGER 102 <SQL_AUTOCOMMIT>
SQLPOINTER 0x00000001
EXIT SQLSetConnectOption with return code 0 (SQL_SUCCESS)
HDBC 0F882AF0
SQLINTEGER 102 <SQL_AUTOCOMMIT>
SQLPOINTER 0x00000001 (BADMEM)
ENTER SQLTransact
HENV 0F881788
HDBC 0F881830
UWORD 1 <SQL_ROLLBACK>
EXIT SQLTransact with return code 0 (SQL_SUCCESS)
HENV 0F881788
HDBC 0F881830
UWORD 1 <SQL_ROLLBACK>
ENTER SQLSetConnectOption
HDBC 0F881830
SQLINTEGER 102 <SQL_AUTOCOMMIT>
SQLPOINTER 0x00000001
EXIT SQLSetConnectOption with return code 0 (SQL_SUCCESS)
HDBC 0F881830
SQLINTEGER 102 <SQL_AUTOCOMMIT>
SQLPOINTER 0x00000001 (BADMEM)
Showing posts with label deadlocks. Show all posts
Showing posts with label deadlocks. Show all posts
Wednesday, March 28, 2012
MDAC 2.8 vs MDAC 2.7 SQL Server Driver - causing deadlocks
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
WCHAR * 0x1B074AA0 [ -3] "SELECT Config, nValue FROM
MSysConf\ 0"
SDWORD -3
DIAG [S0002] [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object
name 'MSysConf'. (208)
ENTER SQLErrorW
HENV 0F881788
HDBC 0F881830
HSTMT 0F8820F0
WCHAR * 0x0C0FE2D0 (NYI)
SDWORD * 0x0C0FE31C
WCHAR * 0x0DFD0048
SWORD 4095
SWORD * 0x0C0FE308
EXIT SQLErrorW with return code 0 (SQL_SUCCESS)
HENV 0F881788
HDBC 0F881830
HSTMT 0F8820F0
WCHAR * 0x0C0FE2D0 (NYI)
SDWORD * 0x0C0FE31C (208)
WCHAR * 0x0DFD0048 [ 78] "[Microsoft][ODBC SQL Server
Driver][SQL Server]Invalid object name 'MSysConf'."
SWORD 4095
SWORD * 0x0C0FE308 (78)
ENTER SQLErrorW
HENV 0F881788
HDBC 0F881830
HSTMT 0F8820F0
WCHAR * 0x0C0FE2D0 (NYI)
SDWORD * 0x0C0FE31C
WCHAR * 0x0DFD00FA
SWORD 4006
SWORD * 0x0C0FE308
EXIT SQLErrorW with return code 100 (SQL_NO_DATA_FOUND)
HENV 0F881788
HDBC 0F881830
HSTMT 0F8820F0
WCHAR * 0x0C0FE2D0 (NYI)
SDWORD * 0x0C0FE31C
WCHAR * 0x0DFD00FA
SWORD 4006
SWORD * 0x0C0FE308
ENTER SQLFreeStmt
HSTMT 0F8820F0
UWORD 1 <SQL_DROP>
EXIT SQLFreeStmt with return code 0 (SQL_SUCCESS)
HSTMT 0F8820F0
UWORD 1 <SQL_DROP>
ENTER SQLGetInfoW
HDBC 0F881830
UWORD 17 <SQL_DBMS_NAME>
PTR 0x0C0FDFC4
SWORD 200
SWORD * 0x0C0FE08E
EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS)
HDBC 0F881830
UWORD 17 <SQL_DBMS_NAME>
PTR 0x0C0FDFC4 [ 40] "Microsoft SQL Server"
SWORD 200
SWORD * 0x0C0FE08E (40)
ENTER SQLGetInfoW
HDBC 0F881830
UWORD 0 <SQL_ACTIVE_CONNECTIONS>
PTR 0x0C0FE0D2
SWORD 2
SWORD * 0x0C0FE0E0
EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS)
HDBC 0F881830
UWORD 0 <SQL_ACTIVE_CONNECTIONS>
PTR 0x0C0FE0D2 (0)
SWORD 2
SWORD * 0x0C0FE0E0 (2)
ENTER SQLGetInfoW
HDBC 0F881830
UWORD 25 <SQL_DATA_SOURCE_READ_ONLY>
PTR 0x0C0FE0B8
SWORD 20
SWORD * 0x0C0FE0E0
EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS)
HDBC 0F881830
UWORD 25 <SQL_DATA_SOURCE_READ_ONLY>
PTR 0x0C0FE0B8 [ 2] "N"
SWORD 20
SWORD * 0x0C0FE0E0 (2)
ENTER SQLGetInfoW
HDBC 0F881830
UWORD 46 <SQL_TXN_CAPABLE>
PTR 0x0C0FE0D0
SWORD 2
SWORD * 0x0C0FE0A0
EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS)
HDBC 0F881830
UWORD 46 <SQL_TXN_CAPABLE>
PTR 0x0C0FE0D0 (2)
SWORD 2
SWORD * 0x0C0FE0A0 (2)
ENTER SQLGetInfoW
HDBC 0F881830
UWORD 23 <SQL_CURSOR_COMMIT_BEHAVIOR>
PTR 0x0C0FE0CC
SWORD 2
SWORD * 0x0C0FE0A0
EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS)
HDBC 0F881830
UWORD 23 <SQL_CURSOR_COMMIT_BEHAVIOR>
PTR 0x0C0FE0CC (1)
SWORD 2
SWORD * 0x0C0FE0A0 (2)
ENTER SQLGetInfoW
HDBC 0F881830
UWORD 24 <SQL_CURSOR_ROLLBACK_BEHAVIOR>
PTR 0x0C0FE0CE
SWORD 2
SWORD * 0x0C0FE0A0
EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS)
HDBC 0F881830
UWORD 24 <SQL_CURSOR_ROLLBACK_BEHAVIOR>
PTR 0x0C0FE0CE (1)
SWORD 2
SWORD * 0x0C0FE0A0 (2)
ENTER SQLGetInfoW
HDBC 0F881830
UWORD 29 <SQL_IDENTIFIER_QUOTE_CHAR>
PTR 0x0C0FE0B8
SWORD 20
SWORD * 0x0C0FE0E0
EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS)
HDBC 0F881830
UWORD 29 <SQL_IDENTIFIER_QUOTE_CHAR>
PTR 0x0C0FE0B8 [ 2] """
SWORD 20
SWORD * 0x0C0FE0E0 (2)
ENTER SQLSetConnectOption
HDBC 0F881830
SQLINTEGER 102 <SQL_AUTOCOMMIT>
SQLPOINTER 0x00000000
EXIT SQLSetConnectOption with return code 0 (SQL_SUCCESS)
HDBC 0F881830
SQLINTEGER 102 <SQL_AUTOCOMMIT>
SQLPOINTER 0x00000000
ENTER SQLAllocStmt
HDBC 0F881830
HSTMT * 0C0FEBD8
EXIT SQLAllocStmt with return code 0 (SQL_SUCCESS)
HDBC 0F881830
HSTMT * 0x0C0FEBD8 ( 0x0f8820f0)
ENTER SQLGetStmtOption
HSTMT 0F8820F0
UWORD 0
PTR 0x0C0FE718
EXIT SQLGetStmtOption with return code 0 (SQL_SUCCESS)
HSTMT 0F8820F0
UWORD 0
PTR 0x0C0FE718
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 SQLBindParameter
HSTMT 0F8820F0
UWORD 1
SWORD 1 <SQL_PARAM_INPUT>
SWORD 4 <SQL_C_LONG>
SWORD 4 <SQL_INTEGER>
SQLULEN 10
SWORD 0
PTR 0x2DFD41A4
SQLLEN 0
SQLLEN * 0x2DFD41A0
EXIT SQLBindParameter with return code 0 (SQL_SUCCESS)
HSTMT 0F8820F0
UWORD 1
SWORD 1 <SQL_PARAM_INPUT>
SWORD 4 <SQL_C_LONG>
SWORD 4 <SQL_INTEGER>
SQLULEN 10
SWORD 0
PTR 0x2DFD41A4
SQLLEN 0
SQLLEN * 0x2DFD41A0 (4)
ENTER SQLExecDirectW
HSTMT 0F8820F0
WCHAR * 0x2DFD61A8 [ -3] "INSERT INTO "dbo"."Events"
("EventMember") VALUES (?)\ 0"
SDWORD -3
EXIT SQLExecDirectW with return code 0 (SQL_SUCCESS)
HSTMT 0F8820F0
WCHAR * 0x2DFD61A8 [ -3] "INSERT INTO "dbo"."Events"
("EventMember") VALUES (?)\ 0"
SDWORD -3
ENTER SQLFreeStmt
HSTMT 0F8820F0
UWORD 3 <SQL_RESET_PARAMS>
EXIT SQLFreeStmt with return code 0 (SQL_SUCCESS)
HSTMT 0F8820F0
UWORD 3 <SQL_RESET_PARAMS>
ENTER SQLRowCount
HSTMT 0F8820F0
SQLLEN * 0x0C0FEBAC
EXIT SQLRowCount with return code 0 (SQL_SUCCESS)
HSTMT 0F8820F0
SQLLEN * 0x0C0FEBAC (1)
ENTER SQLFreeStmt
HSTMT 0F8820F0
UWORD 1 <SQL_DROP>
EXIT SQLFreeStmt with return code 0 (SQL_SUCCESS)
HSTMT 0F8820F0
UWORD 1 <SQL_DROP>
ENTER SQLAllocStmt
HDBC 0F881830
HSTMT * 0C0FE744
EXIT SQLAllocStmt with return code 0 (SQL_SUCCESS)
HDBC 0F881830
HSTMT * 0x0C0FE744 ( 0x0f8820f0)
ENTER SQLGetStmtOption
HSTMT 0F8820F0
UWORD 0
PTR 0x0C0FE6EC
EXIT SQLGetStmtOption with return code 0 (SQL_SUCCESS)
HSTMT 0F8820F0
UWORD 0
PTR 0x0C0FE6EC
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 * 0x1B075CE0 [ -3] "SELECT @.@.IDENTITY\ 0"
SDWORD -3
EXIT SQLExecDirectW with return code 0 (SQL_SUCCESS)
HSTMT 0F8820F0
WCHAR * 0x1B075CE0 [ -3] "SELECT @.@.IDENTITY\ 0"
SDWORD -3
ENTER SQLFetch
HSTMT 0F8820F0
EXIT SQLFetch with return code 0 (SQL_SUCCESS)
HSTMT 0F8820F0
ENTER SQLGetData
HSTMT 0F8820F0
UWORD 1
SWORD 4 <SQL_C_LONG>
PTR <unknown type>
SQLLEN 4
SQLLEN * 0x0C0FE738
EXIT SQLGetData with return code 0 (SQL_SUCCESS)
HSTMT 0F8820F0
UWORD 1
SWORD 4 <SQL_C_LONG>
PTR <unknown type>
SQLLEN 4
SQLLEN * 0x0C0FE738 (4)
ENTER SQLFetch
HSTMT 0F8820F0
EXIT SQLFetch with return code 100 (SQL_NO_DATA_FOUND)
HSTMT 0F8820F0
ENTER SQLFreeStmt
HSTMT 0F8820F0
UWORD 0 <SQL_CLOSE>
EXIT SQLFreeStmt with return code 0 (SQL_SUCCESS)
HSTMT 0F8820F0
UWORD 0 <SQL_CLOSE>
ENTER SQLFreeStmt
HSTMT 0F8820F0
UWORD 1 <SQL_DROP>
EXIT SQLFreeStmt with return code 0 (SQL_SUCCESS)
HSTMT 0F8820F0
UWORD 1 <SQL_DROP>
ENTER SQLAllocConnect
HENV 0F881788
HDBC * 0C0FEBF0
EXIT SQLAllocConnect with return code 0 (SQL_SUCCESS)
HENV 0F881788
HDBC * 0x0C0FEBF0 ( 0x0f882af0)
ENTER SQLSetConnectOption
HDBC 0F882AF0
SQLINTEGER 103 <SQL_LOGIN_TIMEOUT>
SQLPOINTER 0x00000014
EXIT SQLSetConnectOption with return code 0 (SQL_SUCCESS)
HDBC 0F882AF0
SQLINTEGER 103 <SQL_LOGIN_TIMEOUT>
SQLPOINTER 0x00000014 (BADMEM)
ENTER SQLSetConnectAttrW
SQLHDBC 0F882AF0
SQLINTEGER 30002 <unknown>
SQLPOINTER [Unknown attribute 30002]
SQLINTEGER -3
EXIT SQLSetConnectAttrW with return code 0 (SQL_SUCCESS)
SQLHDBC 0F882AF0
SQLINTEGER 30002 <unknown>
SQLPOINTER [Unknown attribute 30002]
SQLINTEGER -3
ENTER SQLDriverConnectW
HDBC 0F882AF0
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 0F882AF0
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 0F882AF0
UWORD 9 <SQL_ODBC_API_CONFORMANCE>
PTR 0x0C0FEC14
SWORD 2
SWORD * 0x0C0FEC0C
EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS)
HDBC 0F882AF0
UWORD 9 <SQL_ODBC_API_CONFORMANCE>
PTR 0x0C0FEC14 (2)
SWORD 2
SWORD * 0x0C0FEC0C (2)
ENTER SQLGetInfoW
HDBC 0F882AF0
UWORD 6 <SQL_DRIVER_NAME>
PTR 0x0C0FEB24
SWORD 200
SWORD * 0x0C0FEC0C
EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS)
HDBC 0F882AF0
UWORD 6 <SQL_DRIVER_NAME>
PTR 0x0C0FEB24 [ 24] "SQLSRV32.DLL"
SWORD 200
SWORD * 0x0C0FEC0C (24)
ENTER SQLGetInfoW
HDBC 0F882AF0
UWORD 46 <SQL_TXN_CAPABLE>
PTR 0x0C0FEAEE
SWORD 2
SWORD * 0x0C0FE6C0
EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS)
HDBC 0F882AF0
UWORD 46 <SQL_TXN_CAPABLE>
PTR 0x0C0FEAEE (2)
SWORD 2
SWORD * 0x0C0FE6C0 (2)
ENTER SQLGetInfoW
HDBC 0F882AF0
UWORD 23 <SQL_CURSOR_COMMIT_BEHAVIOR>
PTR 0x0E029C58
SWORD 2
SWORD * 0x0C0FE6C0
EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS)
HDBC 0F882AF0
UWORD 23 <SQL_CURSOR_COMMIT_BEHAVIOR>
PTR 0x0E029C58 (1)
SWORD 2
SWORD * 0x0C0FE6C0 (2)
ENTER SQLGetInfoW
HDBC 0F882AF0
UWORD 24 <SQL_CURSOR_ROLLBACK_BEHAVIOR>
PTR 0x0E029C5A
SWORD 2
SWORD * 0x0C0FE6C0
EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS)
HDBC 0F882AF0
UWORD 24 <SQL_CURSOR_ROLLBACK_BEHAVIOR>
PTR 0x0E029C5A (1)
SWORD 2
SWORD * 0x0C0FE6C0 (2)
ENTER SQLGetInfoW
HDBC 0F882AF0
UWORD 1 <SQL_ACTIVE_STATEMENTS>
PTR 0x0C0FEAF4
SWORD 2
SWORD * 0x0C0FEADA
EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS)
HDBC 0F882AF0
UWORD 1 <SQL_ACTIVE_STATEMENTS>
PTR 0x0C0FEAF4 (1)
SWORD 2
SWORD * 0x0C0FEADA (2)
ENTER SQLSetConnectOption
HDBC 0F882AF0
SQLINTEGER 101 <SQL_ACCESS_MODE>
SQLPOINTER 0x00000001
EXIT SQLSetConnectOption with return code 0 (SQL_SUCCESS)
HDBC 0F882AF0
SQLINTEGER 101 <SQL_ACCESS_MODE>
SQLPOINTER 0x00000001 (BADMEM)
ENTER SQLGetInfoW
HDBC 0F882AF0
UWORD 17 <SQL_DBMS_NAME>
PTR 0x0C0FEB58
SWORD 200
SWORD * 0x0C0FEC22
EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS)
HDBC 0F882AF0
UWORD 17 <SQL_DBMS_NAME>
PTR 0x0C0FEB58 [ 40] "Microsoft SQL Server"
SWORD 200
SWORD * 0x0C0FEC22 (40)
ENTER SQLSetConnectOption
HDBC 0F882AF0
SQLINTEGER 101 <SQL_ACCESS_MODE>
SQLPOINTER 0x00000001
EXIT SQLSetConnectOption with return code 0 (SQL_SUCCESS)
HDBC 0F882AF0
SQLINTEGER 101 <SQL_ACCESS_MODE>
SQLPOINTER 0x00000001 (BADMEM)
ENTER SQLSetConnectOption
HDBC 0F882AF0
SQLINTEGER 102 <SQL_AUTOCOMMIT>
SQLPOINTER 0x00000000
EXIT SQLSetConnectOption with return code 0 (SQL_SUCCESS)
HDBC 0F882AF0
SQLINTEGER 102 <SQL_AUTOCOMMIT>
SQLPOINTER 0x00000000
ENTER SQLAllocStmt
HDBC 0F882AF0
HSTMT * 320D2174
EXIT SQLAllocStmt with return code 0 (SQL_SUCCESS)
HDBC 0F882AF0
HSTMT * 0x320D2174 ( 0x0f8820f0)
ENTER SQLGetStmtOption
HSTMT 0F8820F0
UWORD 0
PTR 0x0C0FEB7C
EXIT SQLGetStmtOption with return code 0 (SQL_SUCCESS)
HSTMT 0F8820F0
UWORD 0
PTR 0x0C0FEB7C
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 SQLGetStmtOption
HSTMT 0F8820F0
UWORD 3
PTR 0x0C0FEB7C
EXIT SQLGetStmtOption with return code 0 (SQL_SUCCESS)
HSTMT 0F8820F0
UWORD 3
PTR 0x0C0FEB7C
ENTER SQLSetStmtOption
HSTMT 0F8820F0
UWORD 3 <SQL_MAX_LENGTH>
SQLPOINTER 0x7FFFFFFF
EXIT SQLSetStmtOption with return code 0 (SQL_SUCCESS)
HSTMT 0F8820F0
UWORD 3 <SQL_MAX_LENGTH>
SQLPOINTER 0x7FFFFFFF (BADMEM)
ENTER SQLExecDirectW
HSTMT 0F8820F0
WCHAR * 0x320D2690 [ -3] "SELECT "EventKey" ,"EventMember"
,"EventDateFrom" ,"EventDateTo" ,"EventType" ,"EventName" ,"EventDetails"
,"EventAttendanceHere" ,"EventAttendanceOut" ,"EventAttendanceNight"
,"EventCost" ,"EventCostHere" ,"EventCostOut" ,"EventRevenue"
,"EventRevenueIn" ,"EventColIn" ,"EventFamilies" ,"EventContactHrs"
,"EventMeals" ,"EventOtherOrg1" ,"EventOtherOrg2" ,"EventOtherOrg3"
,"EventOtherOrg4" ,"EventOtherOrg5" ,"EventVolunteers" ,"EventVolHrs"
,"EventOutcomeMeasure" ,"EventOutcomeWeight" ,"EventOutcomeNarrative" FROM
"dbo"."Events" WHERE ("EventKey" = 591 ) \ 0"
SDWORD -3
EXIT SQLExecDirectW with return code -1 (SQL_ERROR)
HSTMT 0F8820F0
WCHAR * 0x320D2690 [ -3] "SELECT "EventKey" ,"EventMember"
,"EventDateFrom" ,"EventDateTo" ,"EventType" ,"EventName" ,"EventDetails"
,"EventAttendanceHere" ,"EventAttendanceOut" ,"EventAttendanceNight"
,"EventCost" ,"EventCostHere" ,"EventCostOut" ,"EventRevenue"
,"EventRevenueIn" ,"EventColIn" ,"EventFamilies" ,"EventContactHrs"
,"EventMeals" ,"EventOtherOrg1" ,"EventOtherOrg2" ,"EventOtherOrg3"
,"EventOtherOrg4" ,"EventOtherOrg5" ,"EventVolunteers" ,"EventVolHrs"
,"EventOutcomeMeasure" ,"EventOutcomeWeight" ,"EventOutcomeNarrative" FROM
"dbo"."Events" WHERE ("EventKey" = 591 ) \ 0"
SDWORD -3
DIAG [S1T00] [Microsoft][ODBC SQL Server Driver]Timeout expired (0)
ENTER SQLErrorW
HENV 0F881788
HDBC 0F882AF0
HSTMT 0F8820F0
WCHAR * 0x0C0FEAE8 (NYI)
SDWORD * 0x0C0FEB34
WCHAR * 0x0DFD0048
SWORD 4095
SWORD * 0x0C0FEB20
EXIT SQLErrorW with return code 0 (SQL_SUCCESS)
HENV 0F881788
HDBC 0F882AF0
HSTMT 0F8820F0
WCHAR * 0x0C0FEAE8 (NYI)
SDWORD * 0x0C0FEB34 (0)
WCHAR * 0x0DFD0048 [ 50] "[Microsoft][ODBC SQL Server
Driver]Timeout expired"
SWORD 4095
SWORD * 0x0C0FEB20 (50)
ENTER SQLErrorW
HENV 0F881788
HDBC 0F882AF0
HSTMT 0F8820F0
WCHAR * 0x0C0FEAE8 (NYI)
SDWORD * 0x0C0FEB34
WCHAR * 0x0DFD00BE
SWORD 4036
SWORD * 0x0C0FEB20
EXIT SQLErrorW with return code 100 (SQL_NO_DATA_FOUND)
HENV 0F881788
HDBC 0F882AF0
HSTMT 0F8820F0
WCHAR * 0x0C0FEAE8 (NYI)
SDWORD * 0x0C0FEB34
WCHAR * 0x0DFD00BE
SWORD 4036
SWORD * 0x0C0FEB20
ENTER SQLFreeStmt
HSTMT 0F8820F0
UWORD 1 <SQL_DROP>
EXIT SQLFreeStmt with return code 0 (SQL_SUCCESS)
HSTMT 0F8820F0
UWORD 1 <SQL_DROP>
ENTER SQLTransact
HENV 0F881788
HDBC 0F882AF0
UWORD 1 <SQL_ROLLBACK>
EXIT SQLTransact with return code 0 (SQL_SUCCESS)
HENV 0F881788
HDBC 0F882AF0
UWORD 1 <SQL_ROLLBACK>
ENTER SQLSetConnectOption
HDBC 0F882AF0
SQLINTEGER 102 <SQL_AUTOCOMMIT>
SQLPOINTER 0x00000001
EXIT SQLSetConnectOption with return code 0 (SQL_SUCCESS)
HDBC 0F882AF0
SQLINTEGER 102 <SQL_AUTOCOMMIT>
SQLPOINTER 0x00000001 (BADMEM)
ENTER SQLTransact
HENV 0F881788
HDBC 0F881830
UWORD 1 <SQL_ROLLBACK>
EXIT SQLTransact with return code 0 (SQL_SUCCESS)
HENV 0F881788
HDBC 0F881830
UWORD 1 <SQL_ROLLBACK>
ENTER SQLSetConnectOption
HDBC 0F881830
SQLINTEGER 102 <SQL_AUTOCOMMIT>
SQLPOINTER 0x00000001
EXIT SQLSetConnectOption with return code 0 (SQL_SUCCESS)
HDBC 0F881830
SQLINTEGER 102 <SQL_AUTOCOMMIT>
SQLPOINTER 0x00000001 (BADMEM)
sql
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
WCHAR * 0x1B074AA0 [ -3] "SELECT Config, nValue FROM
MSysConf\ 0"
SDWORD -3
DIAG [S0002] [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object
name 'MSysConf'. (208)
ENTER SQLErrorW
HENV 0F881788
HDBC 0F881830
HSTMT 0F8820F0
WCHAR * 0x0C0FE2D0 (NYI)
SDWORD * 0x0C0FE31C
WCHAR * 0x0DFD0048
SWORD 4095
SWORD * 0x0C0FE308
EXIT SQLErrorW with return code 0 (SQL_SUCCESS)
HENV 0F881788
HDBC 0F881830
HSTMT 0F8820F0
WCHAR * 0x0C0FE2D0 (NYI)
SDWORD * 0x0C0FE31C (208)
WCHAR * 0x0DFD0048 [ 78] "[Microsoft][ODBC SQL Server
Driver][SQL Server]Invalid object name 'MSysConf'."
SWORD 4095
SWORD * 0x0C0FE308 (78)
ENTER SQLErrorW
HENV 0F881788
HDBC 0F881830
HSTMT 0F8820F0
WCHAR * 0x0C0FE2D0 (NYI)
SDWORD * 0x0C0FE31C
WCHAR * 0x0DFD00FA
SWORD 4006
SWORD * 0x0C0FE308
EXIT SQLErrorW with return code 100 (SQL_NO_DATA_FOUND)
HENV 0F881788
HDBC 0F881830
HSTMT 0F8820F0
WCHAR * 0x0C0FE2D0 (NYI)
SDWORD * 0x0C0FE31C
WCHAR * 0x0DFD00FA
SWORD 4006
SWORD * 0x0C0FE308
ENTER SQLFreeStmt
HSTMT 0F8820F0
UWORD 1 <SQL_DROP>
EXIT SQLFreeStmt with return code 0 (SQL_SUCCESS)
HSTMT 0F8820F0
UWORD 1 <SQL_DROP>
ENTER SQLGetInfoW
HDBC 0F881830
UWORD 17 <SQL_DBMS_NAME>
PTR 0x0C0FDFC4
SWORD 200
SWORD * 0x0C0FE08E
EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS)
HDBC 0F881830
UWORD 17 <SQL_DBMS_NAME>
PTR 0x0C0FDFC4 [ 40] "Microsoft SQL Server"
SWORD 200
SWORD * 0x0C0FE08E (40)
ENTER SQLGetInfoW
HDBC 0F881830
UWORD 0 <SQL_ACTIVE_CONNECTIONS>
PTR 0x0C0FE0D2
SWORD 2
SWORD * 0x0C0FE0E0
EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS)
HDBC 0F881830
UWORD 0 <SQL_ACTIVE_CONNECTIONS>
PTR 0x0C0FE0D2 (0)
SWORD 2
SWORD * 0x0C0FE0E0 (2)
ENTER SQLGetInfoW
HDBC 0F881830
UWORD 25 <SQL_DATA_SOURCE_READ_ONLY>
PTR 0x0C0FE0B8
SWORD 20
SWORD * 0x0C0FE0E0
EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS)
HDBC 0F881830
UWORD 25 <SQL_DATA_SOURCE_READ_ONLY>
PTR 0x0C0FE0B8 [ 2] "N"
SWORD 20
SWORD * 0x0C0FE0E0 (2)
ENTER SQLGetInfoW
HDBC 0F881830
UWORD 46 <SQL_TXN_CAPABLE>
PTR 0x0C0FE0D0
SWORD 2
SWORD * 0x0C0FE0A0
EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS)
HDBC 0F881830
UWORD 46 <SQL_TXN_CAPABLE>
PTR 0x0C0FE0D0 (2)
SWORD 2
SWORD * 0x0C0FE0A0 (2)
ENTER SQLGetInfoW
HDBC 0F881830
UWORD 23 <SQL_CURSOR_COMMIT_BEHAVIOR>
PTR 0x0C0FE0CC
SWORD 2
SWORD * 0x0C0FE0A0
EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS)
HDBC 0F881830
UWORD 23 <SQL_CURSOR_COMMIT_BEHAVIOR>
PTR 0x0C0FE0CC (1)
SWORD 2
SWORD * 0x0C0FE0A0 (2)
ENTER SQLGetInfoW
HDBC 0F881830
UWORD 24 <SQL_CURSOR_ROLLBACK_BEHAVIOR>
PTR 0x0C0FE0CE
SWORD 2
SWORD * 0x0C0FE0A0
EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS)
HDBC 0F881830
UWORD 24 <SQL_CURSOR_ROLLBACK_BEHAVIOR>
PTR 0x0C0FE0CE (1)
SWORD 2
SWORD * 0x0C0FE0A0 (2)
ENTER SQLGetInfoW
HDBC 0F881830
UWORD 29 <SQL_IDENTIFIER_QUOTE_CHAR>
PTR 0x0C0FE0B8
SWORD 20
SWORD * 0x0C0FE0E0
EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS)
HDBC 0F881830
UWORD 29 <SQL_IDENTIFIER_QUOTE_CHAR>
PTR 0x0C0FE0B8 [ 2] """
SWORD 20
SWORD * 0x0C0FE0E0 (2)
ENTER SQLSetConnectOption
HDBC 0F881830
SQLINTEGER 102 <SQL_AUTOCOMMIT>
SQLPOINTER 0x00000000
EXIT SQLSetConnectOption with return code 0 (SQL_SUCCESS)
HDBC 0F881830
SQLINTEGER 102 <SQL_AUTOCOMMIT>
SQLPOINTER 0x00000000
ENTER SQLAllocStmt
HDBC 0F881830
HSTMT * 0C0FEBD8
EXIT SQLAllocStmt with return code 0 (SQL_SUCCESS)
HDBC 0F881830
HSTMT * 0x0C0FEBD8 ( 0x0f8820f0)
ENTER SQLGetStmtOption
HSTMT 0F8820F0
UWORD 0
PTR 0x0C0FE718
EXIT SQLGetStmtOption with return code 0 (SQL_SUCCESS)
HSTMT 0F8820F0
UWORD 0
PTR 0x0C0FE718
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 SQLBindParameter
HSTMT 0F8820F0
UWORD 1
SWORD 1 <SQL_PARAM_INPUT>
SWORD 4 <SQL_C_LONG>
SWORD 4 <SQL_INTEGER>
SQLULEN 10
SWORD 0
PTR 0x2DFD41A4
SQLLEN 0
SQLLEN * 0x2DFD41A0
EXIT SQLBindParameter with return code 0 (SQL_SUCCESS)
HSTMT 0F8820F0
UWORD 1
SWORD 1 <SQL_PARAM_INPUT>
SWORD 4 <SQL_C_LONG>
SWORD 4 <SQL_INTEGER>
SQLULEN 10
SWORD 0
PTR 0x2DFD41A4
SQLLEN 0
SQLLEN * 0x2DFD41A0 (4)
ENTER SQLExecDirectW
HSTMT 0F8820F0
WCHAR * 0x2DFD61A8 [ -3] "INSERT INTO "dbo"."Events"
("EventMember") VALUES (?)\ 0"
SDWORD -3
EXIT SQLExecDirectW with return code 0 (SQL_SUCCESS)
HSTMT 0F8820F0
WCHAR * 0x2DFD61A8 [ -3] "INSERT INTO "dbo"."Events"
("EventMember") VALUES (?)\ 0"
SDWORD -3
ENTER SQLFreeStmt
HSTMT 0F8820F0
UWORD 3 <SQL_RESET_PARAMS>
EXIT SQLFreeStmt with return code 0 (SQL_SUCCESS)
HSTMT 0F8820F0
UWORD 3 <SQL_RESET_PARAMS>
ENTER SQLRowCount
HSTMT 0F8820F0
SQLLEN * 0x0C0FEBAC
EXIT SQLRowCount with return code 0 (SQL_SUCCESS)
HSTMT 0F8820F0
SQLLEN * 0x0C0FEBAC (1)
ENTER SQLFreeStmt
HSTMT 0F8820F0
UWORD 1 <SQL_DROP>
EXIT SQLFreeStmt with return code 0 (SQL_SUCCESS)
HSTMT 0F8820F0
UWORD 1 <SQL_DROP>
ENTER SQLAllocStmt
HDBC 0F881830
HSTMT * 0C0FE744
EXIT SQLAllocStmt with return code 0 (SQL_SUCCESS)
HDBC 0F881830
HSTMT * 0x0C0FE744 ( 0x0f8820f0)
ENTER SQLGetStmtOption
HSTMT 0F8820F0
UWORD 0
PTR 0x0C0FE6EC
EXIT SQLGetStmtOption with return code 0 (SQL_SUCCESS)
HSTMT 0F8820F0
UWORD 0
PTR 0x0C0FE6EC
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 * 0x1B075CE0 [ -3] "SELECT @.@.IDENTITY\ 0"
SDWORD -3
EXIT SQLExecDirectW with return code 0 (SQL_SUCCESS)
HSTMT 0F8820F0
WCHAR * 0x1B075CE0 [ -3] "SELECT @.@.IDENTITY\ 0"
SDWORD -3
ENTER SQLFetch
HSTMT 0F8820F0
EXIT SQLFetch with return code 0 (SQL_SUCCESS)
HSTMT 0F8820F0
ENTER SQLGetData
HSTMT 0F8820F0
UWORD 1
SWORD 4 <SQL_C_LONG>
PTR <unknown type>
SQLLEN 4
SQLLEN * 0x0C0FE738
EXIT SQLGetData with return code 0 (SQL_SUCCESS)
HSTMT 0F8820F0
UWORD 1
SWORD 4 <SQL_C_LONG>
PTR <unknown type>
SQLLEN 4
SQLLEN * 0x0C0FE738 (4)
ENTER SQLFetch
HSTMT 0F8820F0
EXIT SQLFetch with return code 100 (SQL_NO_DATA_FOUND)
HSTMT 0F8820F0
ENTER SQLFreeStmt
HSTMT 0F8820F0
UWORD 0 <SQL_CLOSE>
EXIT SQLFreeStmt with return code 0 (SQL_SUCCESS)
HSTMT 0F8820F0
UWORD 0 <SQL_CLOSE>
ENTER SQLFreeStmt
HSTMT 0F8820F0
UWORD 1 <SQL_DROP>
EXIT SQLFreeStmt with return code 0 (SQL_SUCCESS)
HSTMT 0F8820F0
UWORD 1 <SQL_DROP>
ENTER SQLAllocConnect
HENV 0F881788
HDBC * 0C0FEBF0
EXIT SQLAllocConnect with return code 0 (SQL_SUCCESS)
HENV 0F881788
HDBC * 0x0C0FEBF0 ( 0x0f882af0)
ENTER SQLSetConnectOption
HDBC 0F882AF0
SQLINTEGER 103 <SQL_LOGIN_TIMEOUT>
SQLPOINTER 0x00000014
EXIT SQLSetConnectOption with return code 0 (SQL_SUCCESS)
HDBC 0F882AF0
SQLINTEGER 103 <SQL_LOGIN_TIMEOUT>
SQLPOINTER 0x00000014 (BADMEM)
ENTER SQLSetConnectAttrW
SQLHDBC 0F882AF0
SQLINTEGER 30002 <unknown>
SQLPOINTER [Unknown attribute 30002]
SQLINTEGER -3
EXIT SQLSetConnectAttrW with return code 0 (SQL_SUCCESS)
SQLHDBC 0F882AF0
SQLINTEGER 30002 <unknown>
SQLPOINTER [Unknown attribute 30002]
SQLINTEGER -3
ENTER SQLDriverConnectW
HDBC 0F882AF0
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 0F882AF0
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 0F882AF0
UWORD 9 <SQL_ODBC_API_CONFORMANCE>
PTR 0x0C0FEC14
SWORD 2
SWORD * 0x0C0FEC0C
EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS)
HDBC 0F882AF0
UWORD 9 <SQL_ODBC_API_CONFORMANCE>
PTR 0x0C0FEC14 (2)
SWORD 2
SWORD * 0x0C0FEC0C (2)
ENTER SQLGetInfoW
HDBC 0F882AF0
UWORD 6 <SQL_DRIVER_NAME>
PTR 0x0C0FEB24
SWORD 200
SWORD * 0x0C0FEC0C
EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS)
HDBC 0F882AF0
UWORD 6 <SQL_DRIVER_NAME>
PTR 0x0C0FEB24 [ 24] "SQLSRV32.DLL"
SWORD 200
SWORD * 0x0C0FEC0C (24)
ENTER SQLGetInfoW
HDBC 0F882AF0
UWORD 46 <SQL_TXN_CAPABLE>
PTR 0x0C0FEAEE
SWORD 2
SWORD * 0x0C0FE6C0
EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS)
HDBC 0F882AF0
UWORD 46 <SQL_TXN_CAPABLE>
PTR 0x0C0FEAEE (2)
SWORD 2
SWORD * 0x0C0FE6C0 (2)
ENTER SQLGetInfoW
HDBC 0F882AF0
UWORD 23 <SQL_CURSOR_COMMIT_BEHAVIOR>
PTR 0x0E029C58
SWORD 2
SWORD * 0x0C0FE6C0
EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS)
HDBC 0F882AF0
UWORD 23 <SQL_CURSOR_COMMIT_BEHAVIOR>
PTR 0x0E029C58 (1)
SWORD 2
SWORD * 0x0C0FE6C0 (2)
ENTER SQLGetInfoW
HDBC 0F882AF0
UWORD 24 <SQL_CURSOR_ROLLBACK_BEHAVIOR>
PTR 0x0E029C5A
SWORD 2
SWORD * 0x0C0FE6C0
EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS)
HDBC 0F882AF0
UWORD 24 <SQL_CURSOR_ROLLBACK_BEHAVIOR>
PTR 0x0E029C5A (1)
SWORD 2
SWORD * 0x0C0FE6C0 (2)
ENTER SQLGetInfoW
HDBC 0F882AF0
UWORD 1 <SQL_ACTIVE_STATEMENTS>
PTR 0x0C0FEAF4
SWORD 2
SWORD * 0x0C0FEADA
EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS)
HDBC 0F882AF0
UWORD 1 <SQL_ACTIVE_STATEMENTS>
PTR 0x0C0FEAF4 (1)
SWORD 2
SWORD * 0x0C0FEADA (2)
ENTER SQLSetConnectOption
HDBC 0F882AF0
SQLINTEGER 101 <SQL_ACCESS_MODE>
SQLPOINTER 0x00000001
EXIT SQLSetConnectOption with return code 0 (SQL_SUCCESS)
HDBC 0F882AF0
SQLINTEGER 101 <SQL_ACCESS_MODE>
SQLPOINTER 0x00000001 (BADMEM)
ENTER SQLGetInfoW
HDBC 0F882AF0
UWORD 17 <SQL_DBMS_NAME>
PTR 0x0C0FEB58
SWORD 200
SWORD * 0x0C0FEC22
EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS)
HDBC 0F882AF0
UWORD 17 <SQL_DBMS_NAME>
PTR 0x0C0FEB58 [ 40] "Microsoft SQL Server"
SWORD 200
SWORD * 0x0C0FEC22 (40)
ENTER SQLSetConnectOption
HDBC 0F882AF0
SQLINTEGER 101 <SQL_ACCESS_MODE>
SQLPOINTER 0x00000001
EXIT SQLSetConnectOption with return code 0 (SQL_SUCCESS)
HDBC 0F882AF0
SQLINTEGER 101 <SQL_ACCESS_MODE>
SQLPOINTER 0x00000001 (BADMEM)
ENTER SQLSetConnectOption
HDBC 0F882AF0
SQLINTEGER 102 <SQL_AUTOCOMMIT>
SQLPOINTER 0x00000000
EXIT SQLSetConnectOption with return code 0 (SQL_SUCCESS)
HDBC 0F882AF0
SQLINTEGER 102 <SQL_AUTOCOMMIT>
SQLPOINTER 0x00000000
ENTER SQLAllocStmt
HDBC 0F882AF0
HSTMT * 320D2174
EXIT SQLAllocStmt with return code 0 (SQL_SUCCESS)
HDBC 0F882AF0
HSTMT * 0x320D2174 ( 0x0f8820f0)
ENTER SQLGetStmtOption
HSTMT 0F8820F0
UWORD 0
PTR 0x0C0FEB7C
EXIT SQLGetStmtOption with return code 0 (SQL_SUCCESS)
HSTMT 0F8820F0
UWORD 0
PTR 0x0C0FEB7C
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 SQLGetStmtOption
HSTMT 0F8820F0
UWORD 3
PTR 0x0C0FEB7C
EXIT SQLGetStmtOption with return code 0 (SQL_SUCCESS)
HSTMT 0F8820F0
UWORD 3
PTR 0x0C0FEB7C
ENTER SQLSetStmtOption
HSTMT 0F8820F0
UWORD 3 <SQL_MAX_LENGTH>
SQLPOINTER 0x7FFFFFFF
EXIT SQLSetStmtOption with return code 0 (SQL_SUCCESS)
HSTMT 0F8820F0
UWORD 3 <SQL_MAX_LENGTH>
SQLPOINTER 0x7FFFFFFF (BADMEM)
ENTER SQLExecDirectW
HSTMT 0F8820F0
WCHAR * 0x320D2690 [ -3] "SELECT "EventKey" ,"EventMember"
,"EventDateFrom" ,"EventDateTo" ,"EventType" ,"EventName" ,"EventDetails"
,"EventAttendanceHere" ,"EventAttendanceOut" ,"EventAttendanceNight"
,"EventCost" ,"EventCostHere" ,"EventCostOut" ,"EventRevenue"
,"EventRevenueIn" ,"EventColIn" ,"EventFamilies" ,"EventContactHrs"
,"EventMeals" ,"EventOtherOrg1" ,"EventOtherOrg2" ,"EventOtherOrg3"
,"EventOtherOrg4" ,"EventOtherOrg5" ,"EventVolunteers" ,"EventVolHrs"
,"EventOutcomeMeasure" ,"EventOutcomeWeight" ,"EventOutcomeNarrative" FROM
"dbo"."Events" WHERE ("EventKey" = 591 ) \ 0"
SDWORD -3
EXIT SQLExecDirectW with return code -1 (SQL_ERROR)
HSTMT 0F8820F0
WCHAR * 0x320D2690 [ -3] "SELECT "EventKey" ,"EventMember"
,"EventDateFrom" ,"EventDateTo" ,"EventType" ,"EventName" ,"EventDetails"
,"EventAttendanceHere" ,"EventAttendanceOut" ,"EventAttendanceNight"
,"EventCost" ,"EventCostHere" ,"EventCostOut" ,"EventRevenue"
,"EventRevenueIn" ,"EventColIn" ,"EventFamilies" ,"EventContactHrs"
,"EventMeals" ,"EventOtherOrg1" ,"EventOtherOrg2" ,"EventOtherOrg3"
,"EventOtherOrg4" ,"EventOtherOrg5" ,"EventVolunteers" ,"EventVolHrs"
,"EventOutcomeMeasure" ,"EventOutcomeWeight" ,"EventOutcomeNarrative" FROM
"dbo"."Events" WHERE ("EventKey" = 591 ) \ 0"
SDWORD -3
DIAG [S1T00] [Microsoft][ODBC SQL Server Driver]Timeout expired (0)
ENTER SQLErrorW
HENV 0F881788
HDBC 0F882AF0
HSTMT 0F8820F0
WCHAR * 0x0C0FEAE8 (NYI)
SDWORD * 0x0C0FEB34
WCHAR * 0x0DFD0048
SWORD 4095
SWORD * 0x0C0FEB20
EXIT SQLErrorW with return code 0 (SQL_SUCCESS)
HENV 0F881788
HDBC 0F882AF0
HSTMT 0F8820F0
WCHAR * 0x0C0FEAE8 (NYI)
SDWORD * 0x0C0FEB34 (0)
WCHAR * 0x0DFD0048 [ 50] "[Microsoft][ODBC SQL Server
Driver]Timeout expired"
SWORD 4095
SWORD * 0x0C0FEB20 (50)
ENTER SQLErrorW
HENV 0F881788
HDBC 0F882AF0
HSTMT 0F8820F0
WCHAR * 0x0C0FEAE8 (NYI)
SDWORD * 0x0C0FEB34
WCHAR * 0x0DFD00BE
SWORD 4036
SWORD * 0x0C0FEB20
EXIT SQLErrorW with return code 100 (SQL_NO_DATA_FOUND)
HENV 0F881788
HDBC 0F882AF0
HSTMT 0F8820F0
WCHAR * 0x0C0FEAE8 (NYI)
SDWORD * 0x0C0FEB34
WCHAR * 0x0DFD00BE
SWORD 4036
SWORD * 0x0C0FEB20
ENTER SQLFreeStmt
HSTMT 0F8820F0
UWORD 1 <SQL_DROP>
EXIT SQLFreeStmt with return code 0 (SQL_SUCCESS)
HSTMT 0F8820F0
UWORD 1 <SQL_DROP>
ENTER SQLTransact
HENV 0F881788
HDBC 0F882AF0
UWORD 1 <SQL_ROLLBACK>
EXIT SQLTransact with return code 0 (SQL_SUCCESS)
HENV 0F881788
HDBC 0F882AF0
UWORD 1 <SQL_ROLLBACK>
ENTER SQLSetConnectOption
HDBC 0F882AF0
SQLINTEGER 102 <SQL_AUTOCOMMIT>
SQLPOINTER 0x00000001
EXIT SQLSetConnectOption with return code 0 (SQL_SUCCESS)
HDBC 0F882AF0
SQLINTEGER 102 <SQL_AUTOCOMMIT>
SQLPOINTER 0x00000001 (BADMEM)
ENTER SQLTransact
HENV 0F881788
HDBC 0F881830
UWORD 1 <SQL_ROLLBACK>
EXIT SQLTransact with return code 0 (SQL_SUCCESS)
HENV 0F881788
HDBC 0F881830
UWORD 1 <SQL_ROLLBACK>
ENTER SQLSetConnectOption
HDBC 0F881830
SQLINTEGER 102 <SQL_AUTOCOMMIT>
SQLPOINTER 0x00000001
EXIT SQLSetConnectOption with return code 0 (SQL_SUCCESS)
HDBC 0F881830
SQLINTEGER 102 <SQL_AUTOCOMMIT>
SQLPOINTER 0x00000001 (BADMEM)
sql
Monday, March 12, 2012
Maximum Performace with Concurrency and without Deadlocks or Dirty
Hi guys + gals,
I've come to a point in redesigning a database that is basically the reason
we chose to redesign it is for International support and to rid the db and
application code of permitting dirty reads against the database. However,
I'm at the point where I just cannot find a suitable solution other than to
still permit dirty reads due to the nature of the service the db provides.
Here's what 1 table in a database needs to do, but also needs to be able to
support a whole load of concurrent users / connections
A packet of data identifying the userId, the serviceId and a integer value
that the user has selected. The Stored Proc needs to insert the data, then
check that value against the other rows in the table for the same serviceId
which indicates that its either unique, lowest unique or how many other user
s
have that same integer value logged?
As you see from the code below, from the time I check the current status of
the lowest unique number, to adding a value from the user and to recheck the
data, there will have been other users possibly in the hundreds each adding
their own values. Because of this, I either lock the table until the
transaction is completed or break the code up into smaller modular procedure
s
that allow Dirty reads. The main aim is to always feedback the most accurate
data whilst still processing other concurrent users. Is there a better way
of achieving maximum performance and concurrency than what I'm doing without
resorting to dirty reads to prevent deadlocking. I have thought about
denormalising the data to keep current references to the user and value of
the current lowest unique data in another table but I find that I could end
up out of sync with the real data or again I would have to allow dirty reads
or impliment some kind of row versioning all which have cost to performance.
I would appreciate if anyone could offer a good solution or point me in the
right direction to further investivate my problem.
Thanks everyone...
Andy
Here's the DDL
CREATE TABLE [dbo].[UserChoice]
(
[userChoiceId] [int] IDENTITY(1,1) NOT NULL,
[fkUserId] [int] NULL,
[fkServiceId] [int] NULL,
[userValue] [int] NULL,
CONSTRAINT [PK_UserChoice] PRIMARY KEY CLUSTERED
(
[userChoiceId] ASC
)
)
CREATE PROCEDURE UserChoiceInsert
@.UserId INT,
@.ServiceId INT,
@.UserValue INT
AS
IF EXISTS
(
SELECT userValue
FROM UserChoice
WHERE (fkServiceId = @.ServiceId)
AND (fkUserId = @.UserId)
AND (userValue = @.userValue)
)
BEGIN
RAISERROR (N'User already has the value recorded', 10, 1);
RETURN 0
END
DECLARE @.previousLowestUnique INT
SET @.previousLowestUnique = (
SELECT TOP 1 userValue
FROM UserChoice
WHERE (fkServiceId = @.ServiceId)
GROUP BY userValue
Having Count(userValue) = 1)
INSERT INTO UserChoice
(
fkUserId,
fkServiceId,
userValue
)
VALUES
(
@.UserId,
@.ServiceId,
@.userValue
)
DECLARE @.LowestUnique INT
DECLARE @.NumberAtValue INT
DECLARE @.previousUserAtValue INT
SET @.LowestUnique = (
SELECT TOP 1 userValue
FROM UserChoice
WHERE (fkServiceId = @.ServiceId)
GROUP BY userValue
Having Count(userValue) = 1)
SET @.NumberAtValue = (
SELECT COUNT(userValue)
FROM UserChoice
WHERE (fkServiceId = @.ServiceId)
AND (userValue = @.userValue))
PRINT CAST(@.NumberAtValue AS VARCHAR(3)) + ' Number at Value'
PRINT CAST(@.previousLowestUnique AS VARCHAR(3)) + ' Previous Lowest Unique
Value'
PRINT CAST(@.LowestUnique AS VARCHAR(3)) + ' Lowest Unique Value'
PRINT CAST(@.userValue AS VARCHAR(3)) + ' User Value'
DECLARE @.IsLowestUnique BIT
DECLARE @.IsUnique BIT
--Is Lowest Unique
IF ((@.LowestUnique = @.userValue) AND @.NumberAtValue = 1)
BEGIN
PRINT 'Is Lowest Unique'
SET @.IsLowestUnique = 1
END
--no longer lowest unique
ELSE IF ((@.previousLowestUnique = @.userValue) AND @.NumberAtValue = 2)
BEGIN
PRINT 'No longer lowest unique'
SET @.IsLowestUnique = 0
SELECT @.previousUserAtValue = fkUserId
FROM UserChoice
WHERE (fkServiceId = @.ServiceId )
AND (userValue = @.userValue) AND (fkUserId < @.UserId OR fkUserId > @.UserId)
END
--no longer unique
ELSE IF ( @.NumberAtValue > 1)
BEGIN
PRINT 'No longer unique'
SET @.IsLowestUnique = 0
SELECT @.previousUserAtValue = fkUserId
FROM UserChoice
WHERE (fkServiceId = @.ServiceId )
AND (userValue = @.userValue) AND (fkUserId < @.UserId or fkUserId > @.UserId)
END
--Is unique not lowest
ELSE IF ((@.LowestUnique < @.userValue) AND @.NumberAtValue = 1)
BEGIN
PRINT 'Is unique not lowest'
SET @.IsUnique = 1
END
ELSE
BEGIN
PRINT 'Is not unique'
SET @.IsUnique = 0
END
SELECT
@.IsUnique AS IsUnique,
@.IsLowestUnique AS IsLowestUnique,
@.previousUserAtValue as PreviousUserAtValueAndy Furnival (AndyFurnival@.discussions.microsoft.com) writes:
> I've come to a point in redesigning a database that is basically the
> reason we chose to redesign it is for International support and to rid
> the db and application code of permitting dirty reads against the
> database. However, I'm at the point where I just cannot find a suitable
> solution other than to still permit dirty reads due to the nature of the
> service the db provides.
>...
> CREATE TABLE [dbo].[UserChoice]
> (
> [userChoiceId] [int] IDENTITY(1,1) NOT NULL,
> [fkUserId] [int] NULL,
> [fkServiceId] [int] NULL,
> [userValue] [int] NULL,
> CONSTRAINT [PK_UserChoice] PRIMARY KEY CLUSTERED
> (
> [userChoiceId] ASC
> )
> )
I realize that there may be more to the system than you show here, but
judging from the procedure UserChoiceInsert, this table should really
look like:
CREATE TABLE [dbo].[UserChoice]
(
[fkServiceId] [int] NOT NULL,
[userValue] [int] NOT NULL,
[fkUserId] [int] NOT NULL,
CONSTRAINT [PK_UserChoice] PRIMARY KEY CLUSTERED
(
[fkServiceId, userValue, fkUserId] ASC
)
)
That is, these three colunms are are unique, and it appears that it
makes no sense for them to permit NULL.
But what is more important is that with an index on (fkServiceId,
userValue), you can reduce the possibilities for deadlocks considerably.
Your queries now scan the tables several times, which is not good at
all. If two users insert the value for the same service ID simultaneous,
there is still a risk have a risk for deadlocks. One way to avoid this
is to move and modify where you compute @.NumberAtValue:
SELECT @.NumberAtValue = COUNT(*) + 1
FROM UserChoice WITH (UPDLOCK)
WHERE fkServiceID = @.serviceid
AND userValue = @.userValue
The UPDLOCK is shared lock, that only one process at a time can have.
Thus if two users tries into insert the same @.serviceId, userValue at
the same time, one will be held up, until the other commits.
> SET @.previousLowestUnique = (
> SELECT TOP 1 userValue
> FROM UserChoice
> WHERE (fkServiceId = @.ServiceId)
> GROUP BY userValue
> Having Count(userValue) = 1)
You need an ORDER BY here, or else you will just get any unique value.
> INSERT INTO UserChoice
> (
> fkUserId,
> fkServiceId,
> userValue
> )
> VALUES
> (
> @.UserId,
> @.ServiceId,
> @.userValue
> )
Save the insert to the end of the procedure and the transaction. This
means that you reduce the amount of time you hold locks. You would of
course have to modify the logic for this.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
I've come to a point in redesigning a database that is basically the reason
we chose to redesign it is for International support and to rid the db and
application code of permitting dirty reads against the database. However,
I'm at the point where I just cannot find a suitable solution other than to
still permit dirty reads due to the nature of the service the db provides.
Here's what 1 table in a database needs to do, but also needs to be able to
support a whole load of concurrent users / connections
A packet of data identifying the userId, the serviceId and a integer value
that the user has selected. The Stored Proc needs to insert the data, then
check that value against the other rows in the table for the same serviceId
which indicates that its either unique, lowest unique or how many other user
s
have that same integer value logged?
As you see from the code below, from the time I check the current status of
the lowest unique number, to adding a value from the user and to recheck the
data, there will have been other users possibly in the hundreds each adding
their own values. Because of this, I either lock the table until the
transaction is completed or break the code up into smaller modular procedure
s
that allow Dirty reads. The main aim is to always feedback the most accurate
data whilst still processing other concurrent users. Is there a better way
of achieving maximum performance and concurrency than what I'm doing without
resorting to dirty reads to prevent deadlocking. I have thought about
denormalising the data to keep current references to the user and value of
the current lowest unique data in another table but I find that I could end
up out of sync with the real data or again I would have to allow dirty reads
or impliment some kind of row versioning all which have cost to performance.
I would appreciate if anyone could offer a good solution or point me in the
right direction to further investivate my problem.
Thanks everyone...
Andy
Here's the DDL
CREATE TABLE [dbo].[UserChoice]
(
[userChoiceId] [int] IDENTITY(1,1) NOT NULL,
[fkUserId] [int] NULL,
[fkServiceId] [int] NULL,
[userValue] [int] NULL,
CONSTRAINT [PK_UserChoice] PRIMARY KEY CLUSTERED
(
[userChoiceId] ASC
)
)
CREATE PROCEDURE UserChoiceInsert
@.UserId INT,
@.ServiceId INT,
@.UserValue INT
AS
IF EXISTS
(
SELECT userValue
FROM UserChoice
WHERE (fkServiceId = @.ServiceId)
AND (fkUserId = @.UserId)
AND (userValue = @.userValue)
)
BEGIN
RAISERROR (N'User already has the value recorded', 10, 1);
RETURN 0
END
DECLARE @.previousLowestUnique INT
SET @.previousLowestUnique = (
SELECT TOP 1 userValue
FROM UserChoice
WHERE (fkServiceId = @.ServiceId)
GROUP BY userValue
Having Count(userValue) = 1)
INSERT INTO UserChoice
(
fkUserId,
fkServiceId,
userValue
)
VALUES
(
@.UserId,
@.ServiceId,
@.userValue
)
DECLARE @.LowestUnique INT
DECLARE @.NumberAtValue INT
DECLARE @.previousUserAtValue INT
SET @.LowestUnique = (
SELECT TOP 1 userValue
FROM UserChoice
WHERE (fkServiceId = @.ServiceId)
GROUP BY userValue
Having Count(userValue) = 1)
SET @.NumberAtValue = (
SELECT COUNT(userValue)
FROM UserChoice
WHERE (fkServiceId = @.ServiceId)
AND (userValue = @.userValue))
PRINT CAST(@.NumberAtValue AS VARCHAR(3)) + ' Number at Value'
PRINT CAST(@.previousLowestUnique AS VARCHAR(3)) + ' Previous Lowest Unique
Value'
PRINT CAST(@.LowestUnique AS VARCHAR(3)) + ' Lowest Unique Value'
PRINT CAST(@.userValue AS VARCHAR(3)) + ' User Value'
DECLARE @.IsLowestUnique BIT
DECLARE @.IsUnique BIT
--Is Lowest Unique
IF ((@.LowestUnique = @.userValue) AND @.NumberAtValue = 1)
BEGIN
PRINT 'Is Lowest Unique'
SET @.IsLowestUnique = 1
END
--no longer lowest unique
ELSE IF ((@.previousLowestUnique = @.userValue) AND @.NumberAtValue = 2)
BEGIN
PRINT 'No longer lowest unique'
SET @.IsLowestUnique = 0
SELECT @.previousUserAtValue = fkUserId
FROM UserChoice
WHERE (fkServiceId = @.ServiceId )
AND (userValue = @.userValue) AND (fkUserId < @.UserId OR fkUserId > @.UserId)
END
--no longer unique
ELSE IF ( @.NumberAtValue > 1)
BEGIN
PRINT 'No longer unique'
SET @.IsLowestUnique = 0
SELECT @.previousUserAtValue = fkUserId
FROM UserChoice
WHERE (fkServiceId = @.ServiceId )
AND (userValue = @.userValue) AND (fkUserId < @.UserId or fkUserId > @.UserId)
END
--Is unique not lowest
ELSE IF ((@.LowestUnique < @.userValue) AND @.NumberAtValue = 1)
BEGIN
PRINT 'Is unique not lowest'
SET @.IsUnique = 1
END
ELSE
BEGIN
PRINT 'Is not unique'
SET @.IsUnique = 0
END
SELECT
@.IsUnique AS IsUnique,
@.IsLowestUnique AS IsLowestUnique,
@.previousUserAtValue as PreviousUserAtValueAndy Furnival (AndyFurnival@.discussions.microsoft.com) writes:
> I've come to a point in redesigning a database that is basically the
> reason we chose to redesign it is for International support and to rid
> the db and application code of permitting dirty reads against the
> database. However, I'm at the point where I just cannot find a suitable
> solution other than to still permit dirty reads due to the nature of the
> service the db provides.
>...
> CREATE TABLE [dbo].[UserChoice]
> (
> [userChoiceId] [int] IDENTITY(1,1) NOT NULL,
> [fkUserId] [int] NULL,
> [fkServiceId] [int] NULL,
> [userValue] [int] NULL,
> CONSTRAINT [PK_UserChoice] PRIMARY KEY CLUSTERED
> (
> [userChoiceId] ASC
> )
> )
I realize that there may be more to the system than you show here, but
judging from the procedure UserChoiceInsert, this table should really
look like:
CREATE TABLE [dbo].[UserChoice]
(
[fkServiceId] [int] NOT NULL,
[userValue] [int] NOT NULL,
[fkUserId] [int] NOT NULL,
CONSTRAINT [PK_UserChoice] PRIMARY KEY CLUSTERED
(
[fkServiceId, userValue, fkUserId] ASC
)
)
That is, these three colunms are are unique, and it appears that it
makes no sense for them to permit NULL.
But what is more important is that with an index on (fkServiceId,
userValue), you can reduce the possibilities for deadlocks considerably.
Your queries now scan the tables several times, which is not good at
all. If two users insert the value for the same service ID simultaneous,
there is still a risk have a risk for deadlocks. One way to avoid this
is to move and modify where you compute @.NumberAtValue:
SELECT @.NumberAtValue = COUNT(*) + 1
FROM UserChoice WITH (UPDLOCK)
WHERE fkServiceID = @.serviceid
AND userValue = @.userValue
The UPDLOCK is shared lock, that only one process at a time can have.
Thus if two users tries into insert the same @.serviceId, userValue at
the same time, one will be held up, until the other commits.
> SET @.previousLowestUnique = (
> SELECT TOP 1 userValue
> FROM UserChoice
> WHERE (fkServiceId = @.ServiceId)
> GROUP BY userValue
> Having Count(userValue) = 1)
You need an ORDER BY here, or else you will just get any unique value.
> INSERT INTO UserChoice
> (
> fkUserId,
> fkServiceId,
> userValue
> )
> VALUES
> (
> @.UserId,
> @.ServiceId,
> @.userValue
> )
Save the insert to the end of the procedure and the transaction. This
means that you reduce the amount of time you hold locks. You would of
course have to modify the logic for this.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Subscribe to:
Posts (Atom)