Oracle DBLink to SQL Server: Bug?

  • Thread starter Thread starter Leigh Kendall
  • Start date Start date
L

Leigh Kendall

I'm experiencing some very strange behavior with the .NET managed provider
for Oracle 1.1.

We have an Oracle 8.x database with a dblink that points to SQL Server 7. We
have some views and stored procedures that are based on these linked tables.
Selecting from the views and running the stored procedures in a PL/SQL tool
(such as PL/SQL Developer or Toad) works fine.

However, the problem is when we try to access them from the managed Oracle
provider. We get an Oracle error returned: "ora-02041: client database did
not begin a transaction".

If we change the dblink to point to another Oracle server/table, we can get
things to work, AS LONG as we preface things in Oracle with a set
transaction read only statement. When we repoint the DBlink back to SQL
Server, things stop working again and we get the error above.

If we use the System.Data.OleDb driver instead of the
System.Data.OracleClient provider, NO PROBLEMS whatsoever!

Anyone ever run into this or having any workarounds? Is this just some sort
of limitation in the 1.1 managed provider for Oracle?

TIA,
 
Some suggestions:

1. Disable DTC

2. Use the Oracle 9i client.

The new Microsoft Managed Provider for Oracle 1.1 now supports DTC through
the
Oracle MTS solution. This solution first ships with Oracle 9i client. The
Oracle
9i client must installed in order to use distributed transactions.

Note: This may have worked fine in 1.0 since 1.0 didn't support DTC.


Sincerely,

Kevin
Microsoft Support

This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! - www.microsoft.com/security

--------------------
| From: "Leigh Kendall" <[email protected]>
| Subject: Oracle DBLink to SQL Server: Bug?
| Date: Tue, 7 Oct 2003 15:33:31 -0400
| Lines: 28
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
| Message-ID: <[email protected]>
| Newsgroups: microsoft.public.dotnet.framework.adonet
| NNTP-Posting-Host: 159.247.3.210
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP11.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.framework.adonet:63105
| X-Tomcat-NG: microsoft.public.dotnet.framework.adonet
|
| I'm experiencing some very strange behavior with the .NET managed provider
| for Oracle 1.1.
|
| We have an Oracle 8.x database with a dblink that points to SQL Server 7.
We
| have some views and stored procedures that are based on these linked
tables.
| Selecting from the views and running the stored procedures in a PL/SQL
tool
| (such as PL/SQL Developer or Toad) works fine.
|
| However, the problem is when we try to access them from the managed Oracle
| provider. We get an Oracle error returned: "ora-02041: client database did
| not begin a transaction".
|
| If we change the dblink to point to another Oracle server/table, we can
get
| things to work, AS LONG as we preface things in Oracle with a set
| transaction read only statement. When we repoint the DBlink back to SQL
| Server, things stop working again and we get the error above.
|
| If we use the System.Data.OleDb driver instead of the
| System.Data.OracleClient provider, NO PROBLEMS whatsoever!
|
| Anyone ever run into this or having any workarounds? Is this just some
sort
| of limitation in the 1.1 managed provider for Oracle?
|
| TIA,
|
| --
| Leigh Kendall, MCSD, MCDBA
|
|
 
I tried your suggestions; still not working.

I disabled DTC. I also disabled the Oracle MTS Recovery Service. I have the
9.2 client installed.

However, this really isn't a viable solution even if it did work, since
we're using Serviced Components which rely on the above services being
enabled.

I guess we'll have to stick with using the OleDb driver when reading from
Oracle whose tables are linked to SQL Server. Too bad...

Any other suggestions?

Thanks...

Leigh
 
Hello !
However, the problem is when we try to access them from the managed Oracle
provider. We get an Oracle error returned: "ora-02041: client database did
not begin a transaction".
Click to expand...

How are you controlling transactions ? If implicitly,
then probably you should start transaction before
access to DB.

Regards,
Dmitry
 
Tried that too; using ado.net transaction and no transaction.

However, I would think that since the Oracle table is a link to a SQL Server
table, that this would constitute a distributed transaction and that the
ado.net transaction object wouldn't work anyhow. Keep in mind though, that
we're only interested in doing a read only query.

Leigh
 
This sounds like a very interesting scenario, I don't see a lot of people
using distributed transactions (which is an area that I am very interested
in) and I want to know more about how they are being used.

A few things to keep in mind when using distributed transactions, I am sure
that you are already aware of these or you would not be able to run dtc at
all.
Oracle managed provider v1.1 (this is the only provider to require 1.1 for
dtc support)
Oracle Client v 9.0 or 9.1 with Optional component "Support for Microsoft
Distributed transactions" or Oracle Client V9.2 (patched)
The class that you want to use inside of the distributed transaction has to
derive from ServicedComponent
Your assembly needs to have a strong name key
To work with native COM+ components the assembly needs to be regsvcs

An additional caveat is that Oracle does not allow local transactions inside
distributed transactions.

Let me know more information about your scenario and I will try to set it up
locally,
Thanks,
 
Thanks for the reply Angel...

We're currently using distributed transactions in two scenarios:

1. ASP classic with "legacy" VB6 COM components running in COM+ using Oracle
and OleDb.

2. A .NET service which passes data to a Serviced Component which in turn
creates an XML message and writes it to a WebSphere MQ queue and Oracle.

Item 2 was MUCH easier to configure than item 1. I've been using MTS since
VB5 with SQL Server and Oracle and the improvements in .NET for COM+ support
are much welcomed. Especially with Oracle as a back-end.

If you can, can you refer to my original post. The issue I'm really having
relates to linked tables in Oracle that link out to SQL Server. If you need
further details let me know.

Thanks!

--
Leigh Kendall, MCSD, MCDBA

Angel Saenz-Badillos said:
This sounds like a very interesting scenario, I don't see a lot of people
using distributed transactions (which is an area that I am very interested
in) and I want to know more about how they are being used.

A few things to keep in mind when using distributed transactions, I am sure
that you are already aware of these or you would not be able to run dtc at
all.
Oracle managed provider v1.1 (this is the only provider to require 1.1 for
dtc support)
Oracle Client v 9.0 or 9.1 with Optional component "Support for Microsoft
Distributed transactions" or Oracle Client V9.2 (patched)
The class that you want to use inside of the distributed transaction has to
derive from ServicedComponent
Your assembly needs to have a strong name key
To work with native COM+ components the assembly needs to be regsvcs

An additional caveat is that Oracle does not allow local transactions inside
distributed transactions.

Let me know more information about your scenario and I will try to set it up
locally,
Thanks,

--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no rights.
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.
Leigh Kendall said:
Tried that too; using ado.net transaction and no transaction.

However, I would think that since the Oracle table is a link to a SQL Server
table, that this would constitute a distributed transaction and that the
ado.net transaction object wouldn't work anyhow. Keep in mind though, that
we're only interested in doing a read only query.

Leigh

database
did
Click to expand...
Click to expand...
 
This sounds like a very interesting scenario, I don't see a lot of people
using distributed transactions (which is an area that I am very interested
in) and I want to know more about how they are being used.

A few things to keep in mind when using distributed transactions, I am sure
that you are already aware of these or you would not be able to run dtc at
all.
Oracle managed provider v1.1 (this is the only provider to require 1.1 for
dtc support)
Oracle Client v 9.0 or 9.1 with Optional component "Support for Microsoft
Distributed transactions" or Oracle Client V9.2 (patched)
The class that you want to use inside of the distributed transaction has to
derive from ServicedComponent
Your assembly needs to have a strong name key
To work with native COM+ components the assembly needs to be regsvcs

An additional caveat is that Oracle does not allow local transactions inside
distributed transactions.

Let me know more information about your scenario and I will try to set it up
locally,
Thanks,
Click to expand...
I've had this using Oracle's own client. the answer was to set
pooling(?)=false in the connection string. It may not be pooling, if
you care to search in the odp.net forum (hosted on Oracle site) you
will find the right answer. Obviously this may not work with the MS
driver.
 
Tried the Pooling=false attribute in the connection string; still no go.

Angel, if you're reading, can you please review my original post?

Leigh
 
I am going to need some help setting this up, please post some more
information about how I can repro this problem locally, any code, database
schema and dblink information would be helpfull

You may also want to ask about this problem in Oracle related newsgroups,
the main difference between the Oracle managed provider and the oledb driver
is that the oledb driver uses our own mtxoci.dll to controll distributed
transactions and the oracle managed provider relies on Oracle's support for
microsoft dtc.

Thanks
--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no rights.
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.
Leigh Kendall said:
Tried the Pooling=false attribute in the connection string; still no go.

Angel, if you're reading, can you please review my original post?

Leigh

Leigh Kendall said:
Thanks, I'll give it a shot and post back the results.

Leigh

Lyndon Hills said:
On Wed, 8 Oct 2003 09:59:55 -0700, "Angel Saenz-Badillos[MS]"

This sounds like a very interesting scenario, I don't see a lot of people
using distributed transactions (which is an area that I am very interested
in) and I want to know more about how they are being used.

A few things to keep in mind when using distributed transactions, I
Click to expand...
am
sure
that you are already aware of these or you would not be able to run
Click to expand...
dtc
at
all.
Oracle managed provider v1.1 (this is the only provider to require
Click to expand...
1.1
for
dtc support)
Oracle Client v 9.0 or 9.1 with Optional component "Support for Microsoft
Distributed transactions" or Oracle Client V9.2 (patched)
The class that you want to use inside of the distributed transaction
Click to expand...
has
to
derive from ServicedComponent
Your assembly needs to have a strong name key
To work with native COM+ components the assembly needs to be regsvcs

An additional caveat is that Oracle does not allow local transactions inside
distributed transactions.

Let me know more information about your scenario and I will try to
Click to expand...
Click to expand...
set
it
Click to expand...
Click to expand...
 
I am going to need some help setting this up, please post some more
information about how I can repro this problem locally, any code, database
schema and dblink information would be helpfull

You may also want to ask about this problem in Oracle related newsgroups,
the main difference between the Oracle managed provider and the oledb driver
is that the oledb driver uses our own mtxoci.dll to controll distributed
transactions and the oracle managed provider relies on Oracle's support for
microsoft dtc.

Thanks
Click to expand...
Sorry, the connection string attribute is enlist = false, not pooling.
 
Hi Leigh,

Due to the complexity of this issue, if you still have problem regarding
this problem, I suggest you to contact Microsoft tech-support by email or
phone. They will gladly help you further on this problem.


Sincerely,

Kevin
Microsoft Support

This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! - www.microsoft.com/security

--------------------
| From: "Leigh Kendall" <[email protected]>
| References: <[email protected]>
<#[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<#r#[email protected]>
<[email protected]>
| Subject: Re: Oracle DBLink to SQL Server: Bug?
| Date: Sat, 11 Oct 2003 22:34:38 -0400
| Lines: 25
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
| Message-ID: <[email protected]>
| Newsgroups: microsoft.public.dotnet.framework.adonet
| NNTP-Posting-Host: 56.233.252.64.snet.net 64.252.233.56
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.framework.adonet:63445
| X-Tomcat-NG: microsoft.public.dotnet.framework.adonet
|
| Already tried that too; no go. Thanks for the suggestion though...
|
| Leigh
|
| | > On Fri, 10 Oct 2003 16:53:59 -0700, "Angel Saenz-Badillos[MS]"
| >
| > > I am going to need some help setting this up, please post some more
| > >information about how I can repro this problem locally, any code,
| database
| > >schema and dblink information would be helpfull
| > >
| > >You may also want to ask about this problem in Oracle related
newsgroups,
| > >the main difference between the Oracle managed provider and the oledb
| driver
| > >is that the oledb driver uses our own mtxoci.dll to controll
distributed
| > >transactions and the oracle managed provider relies on Oracle's support
| for
| > >microsoft dtc.
| > >
| > >Thanks
| > Sorry, the connection string attribute is enlist = false, not pooling.
|
|
 
Back
Top