Desperate for help on serious TCP/IP connectivity problem
- From: ajmayo@xxxxxxxxxxx
- Date: 20 Jul 2005 03:48:24 -0700
I have posted a detailed discussion of the problem on
microsoft.public.ado. However I am cross-posting this now because we
are very keen to determine if anyone else has had this problem.
Actually desperate might be a more accurate term.
Basically we have discovered that connecting to SQL Server 2000 (or
MSDE either SP2 or SP3a) via TCP/IP under ADO results in queries being
pre-emptable by Windows events under certain circumstances.
This has resulted in random failures in a large VB6 application because
a routine in VB containing an ADO query can be randomly interrupted by
an event, such as a focus change - you can imagine the consequences of
this, because VB code is normally written on the assumption that unless
you yield (i.e call Doevents), then your routine will execute to
completion before any event will be processed.
We have established that ADO queries under both the ODBC (MSDASQL) and
OLEDB drivers are subject to this problem, and that the problem occurs
with various ADO commands e.g opening a recordset or performing an
execute command, for example. We tested under MDAC 2.6 but we believe
the problem also occurs with later releases, and probably lies
somewhere deep in the TCP/IP stack.
The problems do not appear under named pipes, as far as we can
determine. In fact, we are desperately hoping they won't, because there
is no workaround for this issue other than switching to named pipes
that we can think of (after all, if your queries can randomly get
interrupted, you'll need gate flags everywhere - a nightmare!)
The problem will occur if
(a) the target database is on the client machine
(b) the connection to the database is made via TCP/IP
(c) the database needs to perform disk I/O to satisfy the query (using
DBCC DROPCLEANBUFFERS will often provoke the problem, therefore).
We suspect the problem is specific to XP (currently testing on SP1) but
cannot at this stage confirm that it does not occur on Win2K.
The problem has been reproduced on multiple machines and databases, so
we know it is not a specific hardware glitch.
We have observed that a variety of windows messages can interrupt
queries under these circumstances, WM_TIMER and WM_FOCUS messages in
particular.
We suspect that deep in the TCP/IP stack something is pumping messages,
in effect causing the query to 'yield'.
The problem is reproducible both in compiled code and in the VB6
development environment, but we believe VB is only showing the problem,
not responsible for it.
To clarify
...... some code
oConnection.execute sSql
>>>>> this may yield, causing an event to be processed when you don't want it to
...... more code, which you normally would expect to complete
end sub
note that almost any query-invoking command is prone to this problem so
this code will also fail
dim rs as new adodb.recordset
set rs.activeconnection = oConn
rs.open sSql
>>>> can be pre-empted at this point, before the open actually returns to VB
..... more code
end sub
.
- Prev by Date: Re: query too slow
- Next by Date: Incorrect synatax near the keyword DECLARE
- Previous by thread: How to install SQL7 Server components on XP Pro
- Next by thread: Incorrect synatax near the keyword DECLARE
- Index(es):
Relevant Pages
|
