> 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
> >
> >
> >

> 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...
> > > 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
> > > >
> > > >
> > > >
> >
> >
> >

> 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
>
>

> 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
>
>