Q: VB + MS SQL Server and transactions

Q: VB + MS SQL Server and transactions

Post by Gerard Averi » Sun, 05 Mar 1995 02:24:01



Why do these not work:

#1: Any transactions in MySP are always commited

  On Error GoTo DBError

  db.BeginTrans
  n = db.ExecuteSQL("execute sp1 ...")
  ...
  n = db.ExecuteSQL("execute spN ...")
  db.CommitTrans
  Exit Sub

DBError:
  db.Rollback
  Exit Sub

#2: Gives an invalid argument error at db.Execute "begin transaction"

  On Error GoTo DBError

  db.Execute "begin transaction", DB_SQLPASSTHROUGH
  n = db.ExecuteSQL("execute sp1 ...")
  ...
  n = db.ExecuteSQL("execute spN ...")
  db.Execute "commit transaction", DB_SQLPASSTHROUGH
  Exit Sub

DBError:
  db.Execute "rollback transaction", DB_SQLPASSTHROUGH
  Exit Sub

...While this one does work:  

#3: Works fine -- commits and rolls back as expected

  On Error GoTo DBError

  n = db.ExecuteSQL("begin transaction")
  n = db.ExecuteSQL("execute sp1 ...")
  ...
  n = db.ExecuteSQL("execute spN ...")
  n = db.ExecuteSQL("commit transaction")
  Exit Sub

DBError:
  db.ExecuteSQL("rollback transaction")
  Exit Sub

 
 
 

Q: VB + MS SQL Server and transactions

Post by Jay Siss » Wed, 08 Mar 1995 02:39:35



Quote:

>Why do these not work:

>#1: Any transactions in MySP are always commited

  On Error GoTo DBError
  db.BeginTrans
  n = db.ExecuteSQL("execute sp1 ...")
  n = db.ExecuteSQL("execute spN ...")
  db.CommitTrans
  Exit Sub
DBError:
  db.Rollback
  Exit Sub

#1 the BeginTrans and CommitTrans methods are for the Jet database
engine.  ExecuteSQL bypasses the Jet engine and goes directly to
Sybase.  Because of this, the Jet engine has no idea what is
going on with the server and can't commit or roll back a
transaction.

Quote:>#2: Gives an invalid argument error at db.Execute "begin transaction"

  On Error GoTo DBError
  db.Execute "begin transaction", DB_SQLPASSTHROUGH
  n = db.ExecuteSQL("execute sp1 ...")
  n = db.ExecuteSQL("execute spN ...")
  db.Execute "commit transaction", DB_SQLPASSTHROUGH
  Exit Sub
DBError:
  db.Execute "rollback transaction", DB_SQLPASSTHROUGH
  Exit Sub

#1 The Execute method expects a recordset to be
returned.  begin transaction does not return any records
so that is probably why you are getting an error message.

Quote:>#3: Works fine -- commits and rolls back as expected

  On Error GoTo DBError
  n = db.ExecuteSQL("begin transaction")
  n = db.ExecuteSQL("execute sp1 ...")
  n = db.ExecuteSQL("execute spN ...")
  n = db.ExecuteSQL("commit transaction")
  Exit Sub
DBError:
  db.ExecuteSQL("rollback transaction")
  Exit Sub

#3 This is the right way to do it.

Jay

Jay Sissom
Indiana University


 
 
 

1. Q: VB + MS SQL Server and transactions

Why do these not work:

#1: Any transactions in MySP are always commited

  On Error GoTo DBError

  db.BeginTrans
  n = db.ExecuteSQL("execute sp1 ...")
  ...
  n = db.ExecuteSQL("execute spN ...")
  db.CommitTrans
  Exit Sub

DBError:
  db.Rollback
  Exit Sub

#2: Gives an invalid argument error at db.Execute "begin transaction"

  On Error GoTo DBError

  db.Execute "begin transaction", DB_SQLPASSTHROUGH
  n = db.ExecuteSQL("execute sp1 ...")
  ...
  n = db.ExecuteSQL("execute spN ...")
  db.Execute "commit transaction", DB_SQLPASSTHROUGH
  Exit Sub

DBError:
  db.Execute "rollback transaction", DB_SQLPASSTHROUGH
  Exit Sub

...While this one does work:  

#3: Works fine -- commits and rolls back as expected

  On Error GoTo DBError

  n = db.ExecuteSQL("begin transaction")
  n = db.ExecuteSQL("execute sp1 ...")
  ...
  n = db.ExecuteSQL("execute spN ...")
  n = db.ExecuteSQL("commit transaction")
  Exit Sub

DBError:
  db.ExecuteSQL("rollback transaction")
  Exit Sub

2. How to Insert space in the text.

3. Enabling TCP/IP server protocol BUG in SQL2K and SQL Server 6.5 native driver Qs

4. Cursor value inconsistency!

5. MS Transaction Server & SQL Server

6. Record Locking Problem

7. Does SQL Server reuire MS Transaction Server and/or Message Queue

8. SQL Server Service Pack Numbers

9. MS Access Qs

10. GA-Alpharetta-110503--MS SQL Server-DBA Skills-Database Administrator (DBA)-MS SQL Server

11. Need Help: VB4 (32-bit) and MS SQL Server 6.0 give TIMEOUT within a transaction

12. MS Access and SQL Server Transaction Problem

13. PowerBuilder 4 and MS SQL Server and transactions