currently im using VB connect to oracle 8i and 9i, it's a data purging
program which purge data from 8i to 9i.

for example,
db.Open for oracle 8i, and when i execute insert statement into oracle
9i from 8i, it gives me this error message:
ORA-02041: client database did not begin a transaction

but when i tried db.Open for oracle 9i, it won't give any error, it
executes successfully.

the query is very simple,
Insert Into FwdBook_Hbl@Oracle9(COMPANYCODE,BRANCHCODE,JOBNO,H OUSEBLNO)
(SELECT
FwdBook_Hbl.COMPANYCODE,FwdBook_Hbl.BRANCHCODE,Fwd Book_Hbl.JOBNO,FwdBook_Hbl.HOUSEBLNO
FROM FwdBook_Hbl@Oracle8)

i already created database link for these 2 servers.
when i run this query in sql*plus, no problem at all.
ODBC > Disable Microsoft Transaction Server (tick or untick also won't
help)

Am i need to do any setting for oracle 8i?

Thanks...

i never use stored procedure inside my form, it's only a simple insert
select statement, i don't know why there is no problem when db.open in
oracle 9 and error occur in oracle 8..


Set db = New ADODB.Connection
db.Provider = "OraOLEDB.Oracle"
db.Open Oracle8, "max", "max"

db.BeginTrans

sSQL="sql statement...."

db.Execute sSQL

if return True, db.CommitTrans else db.RollbackTrans..


after db.Execute sSQL, it returns error message ORA-02041: client
database did not begin a transaction


skyloon wrote:
> currently im using VB connect to oracle 8i and 9i, it's a data purging
> program which purge data from 8i to 9i.
>
> for example,
> db.Open for oracle 8i, and when i execute insert statement into oracle
> 9i from 8i, it gives me this error message:
> ORA-02041: client database did not begin a transaction
>
> but when i tried db.Open for oracle 9i, it won't give any error, it
> executes successfully.
>
> the query is very simple,
> Insert Into FwdBook_Hbl@Oracle9(COMPANYCODE,BRANCHCODE,JOBNO,H OUSEBLNO)
> (SELECT
> FwdBook_Hbl.COMPANYCODE,FwdBook_Hbl.BRANCHCODE,Fwd Book_Hbl.JOBNO,FwdBook_Hbl.HOUSEBLNO
> FROM FwdBook_Hbl@Oracle8)
>
> i already created database link for these 2 servers.
> when i run this query in sql*plus, no problem at all.
> ODBC > Disable Microsoft Transaction Server (tick or untick also won't
> help)
>
> Am i need to do any setting for oracle 8i?
>
> Thanks...

On 10 Jan 2007, skyloon@gmail.com wrote:
> currently im using VB connect to oracle 8i and 9i, it's a data purging
> program which purge data from 8i to 9i.
>
> for example,
> db.Open for oracle 8i, and when i execute insert statement into oracle
> 9i from 8i, it gives me this error message:
> ORA-02041: client database did not begin a transaction

I've never worked with VB, so, anything I say is just a guess.

Here is what the docs say:

ORA-02041 client database did not begin a transaction

Cause: An update occurred at a coordinated database without the
coordinator beginning a distributed transaction. This may happen if
a stored procedure commits and then performs updates, and the stored
procedure is invoked remotely. It could also happen if an external
transaction monitor violates the XA protocol.

Action: If the cause is the former, check that any commit is
not followed by an update.


> but when i tried db.Open for oracle 9i, it won't give any error, it
> executes successfully.
>
> the query is very simple, Insert Into
> FwdBook_Hbl@Oracle9(COMPANYCODE,BRANCHCODE,JOBNO,H OUSEBLNO) (SELECT
> FwdBook_Hbl.COMPANYCODE,FwdBook_Hbl.BRANCHCODE,Fwd Book_Hbl.JOBNO,FwdBook_Hbl.HOUSEBLNO
> FROM FwdBook_Hbl@Oracle8)

I see that you are using a remote incantation for both the 9i and 8i
databases. Which database is the database your code is logging into,
8i? Maybe try to just make a remote call to the 9i server and remove
the @Oracle8 part of the "FROM" clause? Maybe the remote call to 9i
from a remote 8i database and VB/odbc is confusing things?


> i already created database link for these 2 servers.
> when i run this query in sql*plus, no problem at all.
> ODBC > Disable Microsoft Transaction Server (tick or untick also won't
> help)
>
> Am i need to do any setting for oracle 8i?
>
> Thanks...

--
Galen Boyer