DAO, Transactions, SQLServer

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...b;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
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...b;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
>
>
|||Hello Mike,
Thanks for your quick answer.
I am only getting a "Timeout Expired" Error after 1 minute or so.
I tried your suggestion but with no luck
The whole app is written in DAO so I cannot switch to ADO unfortunately. In
addition I am not even sure ADO will fix that. We always need the most
advanced technology to support the most basic stuff. On the other hand,
client-side cursors, distributed transactions, access database links,
connection pool are available in DAO since early stages...
SerGioGio
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> a crit dans le message de
news:D390368A-C890-4B3E-8C62-725D48FC5348@.microsoft.com...
> 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[vbcol=seagreen]
> 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:
very[vbcol=seagreen]
even[vbcol=seagreen]
not in[vbcol=seagreen]
at[vbcol=seagreen]
connections[vbcol=seagreen]
the[vbcol=seagreen]
...[vbcol=seagreen]
am[vbcol=seagreen]
dbSQLPassThrough[vbcol=seagreen]
|||Hi
Then you need to pass both statements at once (you may have to navigate
through the dataset collection to get to the 2nd executions output) or create
a stored procedure that has the update and select in it. Input parameter of
'mycounter' and output parameter of count.
Regards
Mike
"SerGioGio" wrote:

> Hello Mike,
> Thanks for your quick answer.
> I am only getting a "Timeout Expired" Error after 1 minute or so.
> I tried your suggestion but with no luck
> The whole app is written in DAO so I cannot switch to ADO unfortunately. In
> addition I am not even sure ADO will fix that. We always need the most
> advanced technology to support the most basic stuff. On the other hand,
> client-side cursors, distributed transactions, access database links,
> connection pool are available in DAO since early stages...
> SerGioGio
>
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> a écrit dans le message de
> news:D390368A-C890-4B3E-8C62-725D48FC5348@.microsoft.com...
> worked
> very
> even
> not in
> at
> connections
> the
> ...
> am
> dbSQLPassThrough
>
>
|||Well it is a bit sad to rewrite all the sql just because of one dumb driver
(or whatever it is that fails here).
Using stored proc means writing one version for Oracle, one for Sybase, one
for SQL Server...
But I guess I will have to.
Sometimes I admire MS for their tool/concepts, sometimes I just find they
purposely push us to bloated solutions.
SerGioGio
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> a crit dans le message de
news:76FCBB7F-C446-4938-8122-D5B536D06417@.microsoft.com...
> Hi
> Then you need to pass both statements at once (you may have to navigate
> through the dataset collection to get to the 2nd executions output) or
create
> a stored procedure that has the update and select in it. Input parameter
of[vbcol=seagreen]
> 'mycounter' and output parameter of count.
> Regards
> Mike
>
> "SerGioGio" wrote:
In[vbcol=seagreen]
it)[vbcol=seagreen]
a[vbcol=seagreen]
counter,[vbcol=seagreen]
once,[vbcol=seagreen]
but[vbcol=seagreen]
application[vbcol=seagreen]
sends[vbcol=seagreen]
to[vbcol=seagreen]
I[vbcol=seagreen]
these[vbcol=seagreen]
microsoft[vbcol=seagreen]
you[vbcol=seagreen]
|||Hi SerGioGio,
Close all opened recordset before to begin a transaction.
Vctor 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...b;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
>
|||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...b;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
>