Re: DAO, Transactions, SQLServer
From: SerGioGio (sergiogio_at_yahoo.fr)
Date: 10/01/04
- Next message: Ken Tucker [MVP]: "Re: Unable to log into the database"
- Previous message: JeffCT: "Re: Help with Design"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 1 Oct 2004 10:41:57 +0200
Hello,
OK I eventually found a solution to this issue, hopefully this may help some
people.
I now believe that I was wrong, the responsible for multiple connections is
not ODBC, it is Jet! I was under the assumption that since I was using
dbSQLPassThrough in all my queries, I was getting rid of Jet, but not
completely actually as it turned out.
To completely get rid of Jet one must use VB's ODBCDirect, it's just a
matter of adding the flag dbUseODBC in the Workspace object creation option.
With this option I no longer get outstanding connections, and I have a great
control over the connections.
Hope this will help people not to struggle for a whole week like I did.
SerGioGio
"SerGioGio" <sergiogio@yahoo.fr> a écrit dans le message de
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: Ken Tucker [MVP]: "Re: Unable to log into the database"
- Previous message: JeffCT: "Re: Help with Design"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
