Re: DAO, Transactions, SQLServer
From: Victor Koch ("Victor)
Date: 09/29/04
- Next message: Matt Wyncoop: "RE: Problems connecting with VS.NET Server Explorer"
- Previous message: kennethgoh: "Re: Help:how can i use sql server 2000 in Windows 2003"
- In reply to: SerGioGio: "DAO, Transactions, SQLServer"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 29 Sep 2004 11:00:26 -0300
Hi SerGioGio,
Close all opened recordset before to begin a transaction.
--
Víctor Koch.
"SerGioGio" <sergiogio@yahoo.fr> escribió en el mensaje
news:OyCDm4SpEHA.1588@TK2MSFTNGP09.phx.gbl...
> 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: Matt Wyncoop: "RE: Problems connecting with VS.NET Server Explorer"
- Previous message: kennethgoh: "Re: Help:how can i use sql server 2000 in Windows 2003"
- In reply to: SerGioGio: "DAO, Transactions, SQLServer"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
