Impact of "SET NO_BROWSETABLE ON"?

Impact of "SET NO_BROWSETABLE ON"?

Post by Mark Haman » Thu, 23 Jan 2003 01:18:49



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.)

 
 
 

Impact of "SET NO_BROWSETABLE ON"?

Post by Alejandro Mes » Thu, 23 Jan 2003 02:06:21


Mark,

Are you selecting the data inside a transaction?

Example:

set transaction isolation level read uncommitted

begin transaction
select colA from tableA
commit transaction

AMB

 
 
 

Impact of "SET NO_BROWSETABLE ON"?

Post by Mark Haman » Thu, 23 Jan 2003 02:25:04


Thanks for the reply.  

No, and the MTS object/component that is calling the
stored procedure is setup as "Does not support
transactions".

Quote:>-----Original Message-----
>Mark,

>Are you selecting the data inside a transaction?

>Example:

>set transaction isolation level read uncommitted

>begin transaction
>select colA from tableA
>commit transaction

>AMB

 
 
 

Impact of "SET NO_BROWSETABLE ON"?

Post by Alejandro Mes » Thu, 23 Jan 2003 02:59:35


Mark,

About "SET NO_BROWSETABLE ON", here is a brief explanation from Microsoft.

"
SET NO_BROWSETABLE ON is an undocumented option performed for Remote Data Service (RDS) ActiveX Data Connector (ADC) connections to SQL Server. Enabling this option makes every SELECT statement act as though FOR BROWSE had been appended to the statement, but bypasses the temporary table that FOR BROWSE normally pipes the results through. The net effect is to add keys and timestamps to the query as hidden output columns so the client can update specific rows (updateable cursors) without separate trips to the server to pick up the metadata and using the query to get the appropriate columns.

The NO_BROWSETABLE option in enabled through RDS or ADC-based applications when they elect to use client-side cursors. Using server-side cursors may be a potential workaround for these cases as well.
"

Here is a interesting link about lowering the transaction isolation level for an object component.

http://support.microsoft.com/default.aspx?scid=kb;en-us;215520

For me, it is weird that you want to read dirty data to update, but you know better than us why you are doing this.

AMB

 
 
 

Impact of "SET NO_BROWSETABLE ON"?

Post by Mark Haman » Thu, 23 Jan 2003 03:45:43


Thanks.

What does "FOR BROWSE" do differently than a normal
SELECT?  Does it cause locking?  And why does it go
through temp tables?

We use ADO Disconnected Recordsets throughout our system
[to retrieve, transport, and in the GUI].  However, we
don't connect these recordsets back up to the database to
call updatebatch.  Instead, we pull the data out of the
recordsets and pass that data into stored procedure
parameters.  I could use a Server-Side cursor, but then
I'd have to recreate another Recordset from scratch that I
could populate from the Server side cursor, so that it
is "disconnected" from the database.  This seems too
inefficient.

Ideally, I'd like to get a recordset from SQL Server that
is readonly from SQL's perspective (meaning that it
doesn't need to get the Metadata), but that can be updated
as we pass it around within our system.  I am unaware of a
why to get this unless I was to write my own Provider (and
I'm not about to do that), or if I create a Recordset from
scratch and update the fields/rows from another Recordset.

The Select stored procs are called from non-transactional
MTS components.  So, we can change the Isolation Level.

 
 
 

1. Impact of "SET NO_BROWSETABLE ON"? (entered again with correct email address)

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.)

2. POWERBUILDER & SQL WINDOWS

3. max of ("...","...","..")

4. JDBC in browser (java1.02)

5. DB Login has impact on "RequestLiveness"

6. Error Updating COGNOS PowerPlay Cube on HP-UX Server

7. "Zero Impact" SQL Monitor

8. duplicate rows, but which comlumn ?

9. YEAR 2000 "BUG": IMPACT ON DATABASES AND DBM

10. How to make a "decimal"-field to an "integer"-field

11. "."and ","

12. The ""string""