Prev: Thanks a lot
Next: Checking if a view is being used
From: HMike on 24 May 2010 20:46
I have a customer with SQL 2000. There is a table MEMBER with a datetime
field [FINANCIAL TO] and a view vwFULLMEMBERDETAIL that joins the MEMBER
table with a number of others. The view includes the field [FINANCIAL TO].

With ADO.NET the [FINANCIAL TO] field is updated using a standard UPDATE
statement. Almost immediately after a SELECT on the view is executed to
retreive the [FINANCIAL TO]. These statements are not executed within a
transaction.

My problem is that intermittently the old [FINANCIAL TO] value is returned
rather than the current.

Is it possible the view is returning a cached value and if so how do I
overcome it?



From: Eric Isaacs on 24 May 2010 22:43
A SQL Trace would tell you exactly what his happening. Run the trace
until the unexpected results occur, then review the results of the
trace to see exactly what happened during that update and select.
There are a lot of scenarios that could be at play. It could be that
the value isn't being updated for some unknown reason right now (such
as invalid criteria.) Or it could be that since it's two separate
requests, the second one can be filled first (ex the locks on the
other tables take longer to secure, so the select in the other request
is processed first before the first update finishes. Wrapping it in a
transaction would address that. Also doing the update and returning
the value in a stored procedure would address that outside of a
transactoin as well. It just depends on so much that we can't see
from here. But running a trace should catch it and should help
explain it.

-Eric Isaacs

 | 
Pages: 1
Prev: Thanks a lot
Next: Checking if a view is being used