Posted by bmm on 10/25/07 14:01

Hi again

Sory if my english is bad..

The situation is that I have a configuration table (CONF) which is accesed
often by severel programs (computers) on our network. Some of them are
WEB-applications, some are C# applications and some are manual used
query-analysers.

We noticed, that the C#-application sometimes doesn't get all the data from
the CONF-table when running the specific Stored procedure that reads data
from the CONF-table using NOLOCK. We couldn't get query-analyser to fail the
same way, when we used the same SP. (The SP also reads some other tables
beside the CONF-table. All the selects were using NOLOCK)

The problem was solved when we removed all the NOLOCKs from the SP.

I could understand if the SP returned too many rows in some cases, but not
too few....

/bjarni


"Erland Sommarskog" <esquel@sommarskog.se> skrev i en meddelelse
news:Xns99D1F1D3D751BYazorman@127.0.0.1...
> bmm (bmmsletdetteher@comlog.dk) writes:
>> I have a Store Procedure on a Sql Server 2000 Where I use the Table Hint
>> "NoLock" on all selects.
>>
>> One of my clients (OleDbConnection from C#) doesn't get the same Result
>> Set as the others. The result Set should have 31 rows but this client
>> only gets 5!
>>
>> When I remove all the "NoLocks" everything works fine. How can that be?
>
> Maybe you could clarify a few things.
>
> When you say "client" is that "client" as in "customer" or as in "client
> computer"? Does the client that only gets five rows run the same
> application
> as those that get 31? If they run different applications, doethe other
> application use a different API?
>
> Is this behaviour constistent? That is, does it happen even if there is
> no activity on the system, so that there are no locked rows?
>
> Would it be possible for you to post the code of the procedure?
>
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]