Oracle DBLink to SQL Server: Bug?

[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,

 

[Kevin Sun [MS]]

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
|
|

 

[Leigh Kendall]

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

 

[Dmitry Arefiev]

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".

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

Regards,
Dmitry

 

[Leigh Kendall]

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

 

[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 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,

 

[Leigh Kendall]

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

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.

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

 

[Lyndon Hills]

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,

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.

 

[Leigh Kendall]

Thanks, I'll give it a shot and post back the results.

Leigh

 

[Leigh Kendall]

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

 

[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
--
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.

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

Thanks, I'll give it a shot and post back the results.

Leigh

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

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

 

[Lyndon Hills]

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.

 

[Leigh Kendall]

Already tried that too; no go. Thanks for the suggestion though...

Leigh

 

[Kevin Sun [MS]]

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.
|
|

 

[Leigh Kendall]

Right. I think I may do that. We have support calls to use up anyhow.
Thanks...