Heeelpppp ORA-02041 error!!!!

  • Thread starter Thread starter Norberto Mesén López
  • Start date Start date
N

Norberto Mesén López

Hi,
I got a problem trying to use an Oracle DBLINK into my .NET application.
I created a view between two DataBases. The view, if accesed by Visual
FoxPro across the ODBC it works great.
When I try to view this same view from .NET it doesn't work I get this
error:

ORA-02041 client database did not begin a transaction

Am I missing something???

Regards
Norberto Mesen
 
Hi,
I got a problem trying to use an Oracle DBLINK into my .NET application.
I created a view between two DataBases. The view, if accesed by Visual
FoxPro across the ODBC it works great.
When I try to view this same view from .NET it doesn't work I get this
error:

ORA-02041 client database did not begin a transaction

Am I missing something???

Regards
Norberto Mesen
Click to expand...
I encountered this using the Oracle ODP.net provider. The fix was to
add enlist=false; to the connection string. I only use odp.net, so if
you something else then maybe this won't help, but it can't hurt to
try.
 
Hi Lyndon,

Thanks for your Help. I tried your sugestion but it didn't work. This is
my connection string:
"Data Source=BNS;User
Id=owner;Password=xxxxx;enlist=false;Connection Lifetime=60;Pooling=true;"
Any other ideas??

Regards
Norberto Mesen
 
Hi Lyndon,

Thanks for your Help. I tried your sugestion but it didn't work. This is
my connection string:
"Data Source=BNS;User
Id=owner;Password=xxxxx;enlist=false;Connection Lifetime=60;Pooling=true;"
Any other ideas??

Regards
Norberto Mesen
Click to expand...
I think it would be helpful if you tell us which provider you are
using, there are too many possibilities to guess.

ODBC,
MS OleDB
Oracle OleDB
MS Oracle Provider
Oracle ODP.NET
 
Sorry, you are correct,

MS Oracle Provider

regards
Norberto Mesen
Click to expand...
Since I don't use this provider I can't vouch for it but I've seen it
said that adding

SET TRANSACTION READ ONLY;
whatever select ...
COMMIT;

to a procedure can fix this problem.

If this isn't a stored proc then make a select an anonymous block with
these round the select.

Best of luck!
 
Lyndon,
Thanks for your help. I still haven't been able to get it to work. It is
not a stored procedure, it is a select command.

Well.... I will still keep looking for answers, if you have any more
Ideas thay arew REALLY appreciated.

Regards
Norberto
 
Hi there,

My colleagures and I are experiencing similar problems. The Oracle
software was "upgraded" from version 8.1.7 to version 8.1.7.7.0, incl.
the Oracle ODBC driver. Since then we are getting the same error:
ORA-02041: client database did not begin a transaction. This error
occurs when we try to select records from a view.

The only thing that has worked so far is to use a System DSN based on
the Oracle ODBC driver and to tick the box for option "Connect to
database in read-only mode". Not a very elegant solution. I have tried
to achieve the same programmatically, e.g. like this:

Dim myconstr As String = "Provider=OraOLEDB.Oracle;Data Source=mydsn;"
myconstr = myconstr & "user id=me;password=uwillneverguess"
Dim myDbCon As New OleDbConnection(myconstr)
myDbCon.Open()
Dim myTrans As OleDbTransaction = myDbCon.BeginTransaction()
Dim Contactsql As String = "select * from v_contacts"
Dim Contactcmd As New OleDbCommand(Contactsql,myDbCon)
Dim Contactdr As OleDbDataReader

'Open the connection, read and fill control
Contactcmd.CommandType = CommandType.Text
Contactcmd.Transaction = myTrans
Contactdr = Contactcmd.ExecuteReader()
myTrans.Commit()

However, all to no avail. Have you developed any new ideas in the mean
time? The next thing we are going to try is installing driver version
8.1.7.8.10 dated 29-October-2003.

Kind regards,
Dobedani
Wageningen
The Netherlands
 
Dear All,

Even the following did not work. Error messages are sometimes really
made out of rubbish. I have no further ideas to solve this problem.
Who has? TIA

Kind regards,
Dobedani


Dim myconstr As String = "Provider=OraOLEDB.Oracle;Data Source=mydsn;"
myconstr = myconstr & "user id=me;password=uwillneverguess"
Dim myDbCon As New OleDbConnection(myconstr)
myDbCon.Open()
Dim transSql As String = "SET TRANSACTION READ ONLY"
Dim Contactcmd As New OleDbCommand(TransSql, myDbCon)
Contactcmd.CommandType = CommandType.Text
Contactcmd.ExecuteNonQuery()

Dim Contactsql As String = "select * from v_contacts"
Dim Contactdr As OleDbDataReader

'Open the connection, read and fill control
Contactcmd.CommandText = Contactsql
Contactdr = Contactcmd.ExecuteReader()
ContactsRepeater.DataSource= Contactdr
ContactsRepeater.Databind()
Contactdr.Close()

'Clean up
Contactcmd.CommandText = "COMMIT"
Contactcmd.ExecuteNonQuery()
Contactcmd.Dispose()
myDbCon.Close()
 
Dear All,

Even the following did not work. Error messages are sometimes really
made out of rubbish. I have no further ideas to solve this problem.
Who has? TIA

Kind regards,
Dobedani


Dim myconstr As String = "Provider=OraOLEDB.Oracle;Data Source=mydsn;"
myconstr = myconstr & "user id=me;password=uwillneverguess"
Dim myDbCon As New OleDbConnection(myconstr)
myDbCon.Open()
Dim transSql As String = "SET TRANSACTION READ ONLY"
Dim Contactcmd As New OleDbCommand(TransSql, myDbCon)
Contactcmd.CommandType = CommandType.Text
Contactcmd.ExecuteNonQuery()

Dim Contactsql As String = "select * from v_contacts"
Dim Contactdr As OleDbDataReader

'Open the connection, read and fill control
Contactcmd.CommandText = Contactsql
Contactdr = Contactcmd.ExecuteReader()
ContactsRepeater.DataSource= Contactdr
ContactsRepeater.Databind()
Contactdr.Close()

'Clean up
Contactcmd.CommandText = "COMMIT"
Contactcmd.ExecuteNonQuery()
Contactcmd.Dispose()
myDbCon.Close()
Click to expand...
Is it impossible to change provider to ODP.NET (from Oracle)? I know
that you can solve the problem using that.
 
Lyndon said:
Is it impossible to change provider to ODP.NET (from Oracle)? I know
that you can solve the problem using that.
Click to expand...

Dear All,
I had the same issue ORA-02041 error. I tried the above example and I
did not have any success. I was successful in getting this to work. I
reversed the Sql string and connected to the other database. Please
look at the example below.

Example
ERROR SQL:
SELECT A.1, B.1,B.2
FROM USER@ORACLEDB A,LOGIN B
WHERE B.3 = A.3

WORKING SQL:
SELECT A.1, B.1,B.2
FROM USER A,LOGIN@THISSERVER B
WHERE B.3 = A.3

Best Luck,
Bill
 
Why wrap this in a transaction rather than use a stored procedure? Your
first statment merely reduces load on the database and avoids locks. As a
SELECT only batch is unlikely to lock, it is likely to be unnecessary.

ODP.NET? Love it and prefer to MS when working with Oracle. Nothing against
OracleClient, but Oracle changes internals like most of us change
underclothes, leaving third parties (including MS) in the lurch.

---
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

************************************
Think outside the box!
************************************
 
Back
Top