Begintrans-Commit with SQL-Server7?? 
Author Message
 Begintrans-Commit with SQL-Server7??

I am programming database access from Visual Basic normally using MS
Access97 as the database (via Jet DAO). Now I have worked on
transferring to SQL-Server7 and I have this strange problem with
transactions:
If I try to protect a number of updates with a transaction like below
I get an error from SQL-Server after the program has hung for about a
minute or so:
MyDatabase.BeginTrans
        sSql = <some action query>
        MyDatabase.Execute sSql
        sSql = <some action query>
        MyDatabase.Execute sSql
        sSql = <some action query>
        MyDatabase.Execute sSql
MyDatabase.CommitTrans

the different queries are there to modify various tables that need to
have corresponding data when finished. I have the following code in my
On Error handler:
MyDatabase.Rollback

The thing is that I always wind up in the error handler when the
database is an SQL-Server database connected through ODBC, but
everything works fine when it is an Access97 database with the same
structure.

Any thoughts?

Bo Berglund
Software developer in Sweden

PGP: My public key is available at the following locations:
Idap://certserver.pgp.com
http://www.*-*-*.com/ :11371



Thu, 21 Nov 2002 03:00:00 GMT  
 Begintrans-Commit with SQL-Server7??
More details on the problem:
It seems like this is what is causing the problem with transaction
handling:

BeginTrans
        Action query that inserts records
        Select query that retrieves a few records
        Action query that inserts or modifies records
        Select query that gets some values
        Action query that updates the final records
CommitTrans

When I trace through this I get the error at the first SELECT query.
ODBC actually times out here.

Qusetion:
Is it not possible to make a few selections inside a transaction to
get data to further process??

Bo Berglund
Software developer in Sweden

PGP: My public key is available at the following locations:
Idap://certserver.pgp.com
http://pgpkeys.mit.edu:11371



Thu, 21 Nov 2002 03:00:00 GMT  
 Begintrans-Commit with SQL-Server7??
bo,
there's nothing wrong with a select statement because it isn't a logged
operation. but if your sql statment is timing out then you need to fix
that first before assuming transaction problems.
robert


Quote:

> More details on the problem:
> It seems like this is what is causing the problem with transaction
> handling:

> BeginTrans
>    Action query that inserts records
>    Select query that retrieves a few records
>    Action query that inserts or modifies records
>    Select query that gets some values
>    Action query that updates the final records
> CommitTrans

> When I trace through this I get the error at the first SELECT query.
> ODBC actually times out here.

> Qusetion:
> Is it not possible to make a few selections inside a transaction to
> get data to further process??

> Bo Berglund
> Software developer in Sweden

> PGP: My public key is available at the following locations:
> Idap://certserver.pgp.com
> http://pgpkeys.mit.edu:11371

Sent via Deja.com http://www.deja.com/
Before you buy.


Fri, 22 Nov 2002 03:00:00 GMT  
 Begintrans-Commit with SQL-Server7??
Exactly at what point does it hang?

Jonathan Allen


Quote:
> I am programming database access from Visual Basic normally using MS
> Access97 as the database (via Jet DAO). Now I have worked on
> transferring to SQL-Server7 and I have this strange problem with
> transactions:
> If I try to protect a number of updates with a transaction like below
> I get an error from SQL-Server after the program has hung for about a
> minute or so:
> MyDatabase.BeginTrans
> sSql = <some action query>
> MyDatabase.Execute sSql
> sSql = <some action query>
> MyDatabase.Execute sSql
> sSql = <some action query>
> MyDatabase.Execute sSql
> MyDatabase.CommitTrans

> the different queries are there to modify various tables that need to
> have corresponding data when finished. I have the following code in my
> On Error handler:
> MyDatabase.Rollback

> The thing is that I always wind up in the error handler when the
> database is an SQL-Server database connected through ODBC, but
> everything works fine when it is an Access97 database with the same
> structure.

> Any thoughts?

> Bo Berglund
> Software developer in Sweden

> PGP: My public key is available at the following locations:
> Idap://certserver.pgp.com
> http://pgpkeys.mit.edu:11371



Fri, 22 Nov 2002 03:00:00 GMT  
 Begintrans-Commit with SQL-Server7??
On Mon, 5 Jun 2000 00:01:38 -0700, "Jonathan Allen"

Quote:

>Exactly at what point does it hang?

>Jonathan Allen

The sequence hangs exactly when the first select query is used to
create a recordset. Like this:

MyWorkSpace.BeginTrans

sSql = "DELETE FROM OPORDER WHERE AONR = '123A';"
MyDatabase.Execute sSql, dbFailOnError

sSql="INSERT INTO OPORDER ....."
MyDatabase.Execute sSql, dbFailOnError

sSql="SELECT AONR, OP, QTY FROM OPORDER WHERE...."
Set RS = MyDatabase.OpenRecordset(sSql. dbOpenSnapshot) <- fails

if not RS.EOF then
nQty = RS.Fields("QTY").Value
endif

sSql = "UPDATE ORDER SET QTY = " & nQty & " WHERE AONR='123A';"
MyDatabase.Execute sSql, dbFailOnError

<snip>

MyWorkSpace.CommitTrans

It looks like the SQLServer engine is waiting for the commit until it
serves the recordset. If I step to the point where the recordset
should be created and then use the Query Analyzer from SQLServer to
execute any query on the OPORDER table the result is the same, there
is an indefinite wait and finally it fails.
So unlike Access it seems like the result of previous operations in a
transaction is not available even inside the transaction frame. As you
see I have modified the OPORDER table before using the select query to
get data from it and this halts the process. If I instead make a
select query to some other table than OPORDER it is OK, these work
good.

Bo Berglund
Software developer in Sweden

PGP: My public key is available at the following locations:
Idap://certserver.pgp.com
http://pgpkeys.mit.edu:11371



Fri, 22 Nov 2002 03:00:00 GMT  
 Begintrans-Commit with SQL-Server7??
Hello Bo,
if you say "The sequence hangs exactly when the first select query is
used..."
I think the previous statement (Insert...) failed. The reason may be that
rows of the table
should first to be deleted and second to be inserted.
In such a situation I prefered a solution with markers (flags),
that means the first operation (delete) should be splittet in:

1. Update <table> set marker = "to be deleted" where <id> = <id_value>
2. (at later time) delete from <table> where marker = "to be deleted"

Or you could try simply
to change your competing operations, insert before delete

Mario Zitek
Software developer in Jena, Thuringia, German



Sat, 23 Nov 2002 03:00:00 GMT  
 
 [ 6 post ] 

 Relevant Pages 

1. Using BeginTrans, Commit Trans

2. VB4 - Commit or Rollback without BeginTrans. (Error 3034)

3. BeginTrans Commit RollBack

4. BeginTrans, Commit, RollBack

5. Commit or Rollback without BeginTrans Error

6. Cost of Begintrans/Commit

7. VB4 - Commit or Rollback without BeginTrans. (Error 3034)

8. BeginTrans|Commit|Rollback

9. Oracle commits vs Sybase/SQL-Server commits

10. Begintrans,commit Blocking

11. Begintrans/Commit/Rollback

12. VB3 err:Commit or RollBack without BeginTrans, but the BeginTrans is there!

 

 
Powered by phpBB® Forum Software