setting transaction isolation level programatically

setting transaction isolation level programatically

Post by Faustino Din » Sat, 04 May 2002 01:20:28



Hi everybody,

I need to do a
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
but when connecting to the database by ADO
From the SQLServer 2000 documentation it should work
conn.Properties("Isolation Levels") = 256;   //DBPROPVAL_TI_READUNCOMMITTED;
but I get the following error:
"Item cannot be found in the collection corresponding to the requested name
or ordinal"
It looks like that Property doesn'n exist.
What can I do?

Thanks in advance
   Faustino

 
 
 

setting transaction isolation level programatically

Post by Aaron Bertrand [MVP » Sat, 04 May 2002 01:25:25


How are you connecting to SQL Server?  The SQL Server driver, the OLEDB
provider, a DSN, ???

www.aspfaq.com


Quote:> Hi everybody,

> I need to do a
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> but when connecting to the database by ADO
> From the SQLServer 2000 documentation it should work
> conn.Properties("Isolation Levels") = 256;

//DBPROPVAL_TI_READUNCOMMITTED;
Quote:> but I get the following error:
> "Item cannot be found in the collection corresponding to the requested
name
> or ordinal"
> It looks like that Property doesn'n exist.
> What can I do?

> Thanks in advance
>    Faustino


 
 
 

setting transaction isolation level programatically

Post by Narayana Vyas Kondredd » Sat, 04 May 2002 01:38:47


I don't think we have "Isolation levels" in the connection properties
collection. You should use the IsolationLevel property of Connection object.
For example:

c.IsolationLevel = adXactReadUncommitted
--
HTH,
Vyas, MVP (SQL Server)

http://vyaskn.tripod.com/


Quote:> Hi everybody,

> I need to do a
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> but when connecting to the database by ADO
> From the SQLServer 2000 documentation it should work
> conn.Properties("Isolation Levels") = 256;

//DBPROPVAL_TI_READUNCOMMITTED;
Quote:> but I get the following error:
> "Item cannot be found in the collection corresponding to the requested
name
> or ordinal"
> It looks like that Property doesn'n exist.
> What can I do?

> Thanks in advance
>    Faustino

 
 
 

setting transaction isolation level programatically

Post by Faustino Din » Sat, 04 May 2002 01:44:07


I found a workaround

 conn.IsolationLevel = adXactReadUncommitted;     //256
DBPROPVAL_TI_READUNCOMMITTED;

Any way, it doesn't works as specified in the documentation
conn.Properties("Isolation Levels")
(SQL Server Books Online: Isolation Levels Property)

Why?


Quote:> Hi everybody,

> I need to do a
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> but when connecting to the database by ADO
> From the SQLServer 2000 documentation it should work
> conn.Properties("Isolation Levels") = 256;

//DBPROPVAL_TI_READUNCOMMITTED;
Quote:> but I get the following error:
> "Item cannot be found in the collection corresponding to the requested
name
> or ordinal"
> It looks like that Property doesn'n exist.
> What can I do?

> Thanks in advance
>    Faustino

 
 
 

setting transaction isolation level programatically

Post by Aaron Bertrand [MVP » Sat, 04 May 2002 01:45:58


The following BOL article seems to think the property does exist:

    adosql.chm::/adoprg04_82gp.htm

However it implies that you must be connecting via OLE-DB.

www.aspfaq.com



Quote:> I don't think we have "Isolation levels" in the connection properties
> collection. You should use the IsolationLevel property of Connection
object.
> For example:

> c.IsolationLevel = adXactReadUncommitted

 
 
 

setting transaction isolation level programatically

Post by Narayana Vyas Kondredd » Sat, 04 May 2002 02:00:19


...then it must be populating the Properties collection with "Isolation
levels" AFTER you establish the connection.
--
HTH,
Vyas, MVP (SQL Server)

http://vyaskn.tripod.com/



> The following BOL article seems to think the property does exist:

>     adosql.chm::/adoprg04_82gp.htm

> However it implies that you must be connecting via OLE-DB.

> www.aspfaq.com



> > I don't think we have "Isolation levels" in the connection properties
> > collection. You should use the IsolationLevel property of Connection
> object.
> > For example:

> > c.IsolationLevel = adXactReadUncommitted

 
 
 

setting transaction isolation level programatically

Post by Aaron Bertrand [MVP » Sat, 04 May 2002 02:43:20


Quite possible, yes.

www.aspfaq.com

Quote:> ...then it must be populating the Properties collection with "Isolation
> levels" AFTER you establish the connection.

 
 
 

setting transaction isolation level programatically

Post by Alan Brewer [MS » Sat, 04 May 2002 04:08:39


I have not written an ADO app, so I cannot say definitively what the problem
is. I have forwarded this issue to the writer who now covers ADO in the SQL
Server Books Online and asked her to clarify the use of these properties in
the next release of the Books Online.

I would not regard the use of the adXactReadUncommitted property as a
workaround since it is a part of the ADO specification itself, and not
provider-specific.

--
This posting is provided "as-is" with no warranties, and confers no rights.

Alan Brewer
Technology Lead
SQL Server Documentation Team

 
 
 

1. setting transaction isolation level on connection

This is probably posted in the wrong group and is a simple question, but:

For report purposes I'd like to have the isolation level set to
READ UNCOMMITTED or "dirty reads" or "no lock". In our application that
would be acceptable.  I can't count on our users to set this when
logging in.

Is there any way to configure SQL server for this default?
OR
Is there a way to run a stored procedure upon login where I can
place the SQL command to set the isolation level?

Thanks for any help

Michael Eno

2. connection concentrator

3. transaction isolation level setting serializable - deadlocks

4. EXPERIENCED PROGRAMMER is looking for an interesting job - Corp. Greed is the Problem

5. set transaction isolation level

6. PKZIP in Foxpro Windows

7. Where should I put SET TRANSACTION ISOLATION LEVEL?

8. O7.3 Updatable join view: key-preserved table

9. SET TRANSACTION ISOLATION LEVEL

10. Setting Transaction Isolation Level through ODBC

11. How to set Transaction Isolation Level?

12. Set Transaction ISOLATION LEVEL Serializable

13. SET TRANSACTION ISOLATION LEVEL BUG?