RE: DAO, Transactions, SQLServer
From: Mike Epprecht (SQL MVP) (mike_at_epprecht.net)
Date: 09/28/04
- Next message: SerGioGio: "Re: DAO, Transactions, SQLServer"
- Previous message: SerGioGio: "DAO, Transactions, SQLServer"
- In reply to: SerGioGio: "DAO, Transactions, SQLServer"
- Next in thread: SerGioGio: "Re: DAO, Transactions, SQLServer"
- Reply: SerGioGio: "Re: DAO, Transactions, SQLServer"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 28 Sep 2004 01:51:02 -0700
Hi
What error are you getting?
Have you considered using ADO insterad of DAO?
DAO is a very old technology so I am trying to remember how the stuff worked
10 years ago.
Since you are doing a read, use dbForwardOnly (I think that is what is it)
for the rs.
rs.BeginTran and rs.CommitTran are required.
Regards
Mike
"SerGioGio" wrote:
> Hello,
>
> I have a problem with DAO, Transactions and SQLServer. I want to do a very
> simple thing (in VB)!
>
> BeginTrans
> Call db.Execute("UPDATE counter SET value = value + 1 WHERE name =
> 'mycounter'", dbSQLPassThrough)
> Set Rec = db.OpenRecordSet("SELECT value FROM counter WHERE name =
> 'mycounter'", dbOpenSnapshot, dbSQLPassThrough)
> value = Rec(0)
> CommitTrans
>
> Note how simple this is. I just want to get a new value for a counter,
> UPDATing first in order to make sure each value is returned only once, even
> in concurrent environment. This is what you learn in school.
> Well, this code works in Oracle, Sybase, in SQLServer using OSQL, but not in
> SQLServer using DAO/ODBC.
> In SQLServer using DAO/ODBC the code just hangs the entire application at
> the SELECT line.
>
> I believe the problem comes from the ODBC SQL Server driver
> (2000.85.1025.00). From SQLServer OSQL I can see that multiple connections
> are opened (by the driver I believe) and I suspect that the driver sends the
> UPDATE statement to connection 1 for instance and the SELECT query to ...
> connection 2! Of course this will cause a deadlock.
>
> I saw microsoft comments
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;170548 , but I am
> not really using JET, since all my database calls use the dbSQLPassThrough
> option. I also tried to disable the ODBC connection pool but this is
> useless. I believe it really is the Drivers fault since I can get these
> statements to work in Sybase and Oracle.
>
> This is the kind of stuff that puzzles me the most. The whole microsoft
> architecture tries to be smarter than you are and takes control of
> everything, but fails to do the simplest things AND it really seems you
> cannot disable it.
>
> Any help would be very much appreciated.
>
> SerGioGio
>
>
>
- Next message: SerGioGio: "Re: DAO, Transactions, SQLServer"
- Previous message: SerGioGio: "DAO, Transactions, SQLServer"
- In reply to: SerGioGio: "DAO, Transactions, SQLServer"
- Next in thread: SerGioGio: "Re: DAO, Transactions, SQLServer"
- Reply: SerGioGio: "Re: DAO, Transactions, SQLServer"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
