I'm experiencing a blocking problem where my stored
procedures (that select/retrieve data) are getting
blocked. Our SQL Server is SQLServer2000 Ent. Ed. with
SP2, running on Windows2000 Advanced Server SP3. I'm
using MDAC 2.7 (SQLOLEDB provider) within MTS 2.0 (on
WinNT 4.0 SP6a), and I use Cursor location of adUseClient,
Cursor Type of adOpenStatic, and Lock Type of
adLockBatchOptimistic. I call "SET TRANSACTION ISOLATION
LEVEL READ UNCOMMITTED" before calling the stored
procedure, and all stored procedures have the "With
(NOLOCK)" hint (I want dirty reads). I don't understand
how my stored procedures can get blocked using Isolation
Level of Read Uncommitted.
I noticed (using SQL profiler) that all of my "Select"
stored procedure calls execute the "SET NO_BROWSETABLE ON"
statement before executing my stored procedure. What
impact does the "SET NO_BROWSETABLE ON" statement have?
(I noticed that if I use Lock Type of adLockReadOnly,
the "SET NO_BROWSETABLE ON" statement is NOT executed.)