linking two datasets: base

server to Oracle using SQL Server 2005

I am trying to set a linked server for an Oracle base(9.2) to SQL Server
2005(June CTP).
I am able to set up the linked server but when I execute a query then I get
the following error
The OLE DB provider "MSDAORA.1" for linked server "ORACLDB" reported an
error. Access denied.
Msg 7301, Level 16, State 2, Line 1
Cannot obtain the required interface ("IID_IDBCreateCommand") from OLE DB
provider "MSDAORA.1" for linked server "ORACLDB".
I am able to connect to this ORACLE base using SQL PLUS. I am also able to
extract data using SSIS.
Thanks
ShriInstall the Oracle Client tools should help.
You need to have Oracle Client installed
--
Dandy Weyn
[MCSE-MCSA-MCDBA-MCDST-MCT]
http://www.dandyman.net
Check my SQL Server Resource Pages at http://www.dandyman.net/sql
"Shri Rao" <Shri Rao@.discussions.microsoft.com> wrote in message
news:1E6D4028-A7B3-4995-B71B-83FBB8FFB4B5@.microsoft.com...
>I am trying to set a linked server for an Oracle base(9.2) to SQL Server
> 2005(June CTP).
> I am able to set up the linked server but when I execute a query then I
> get
> the following error
> The OLE DB provider "MSDAORA.1" for linked server "ORACLDB" reported an
> error. Access denied.
> Msg 7301, Level 16, State 2, Line 1
> Cannot obtain the required interface ("IID_IDBCreateCommand") from OLE DB
> provider "MSDAORA.1" for linked server "ORACLDB".
>
> I am able to connect to this ORACLE base using SQL PLUS. I am also able to
> extract data using SSIS.
> Thanks
> Shri|||I do have the Oracle client installed and I am able to connect using SQL+, I
am also able to extract the data using SSIS.
"Dandy Weyn [Dandyman]" wrote:
> Install the Oracle Client tools should help.
> You need to have Oracle Client installed
> --
> Dandy Weyn
> [MCSE-MCSA-MCDBA-MCDST-MCT]
> http://www.dandyman.net
> Check my SQL Server Resource Pages at http://www.dandyman.net/sql
> "Shri Rao" <Shri Rao@.discussions.microsoft.com> wrote in message
> news:1E6D4028-A7B3-4995-B71B-83FBB8FFB4B5@.microsoft.com...
> >I am trying to set a linked server for an Oracle base(9.2) to SQL Server
> > 2005(June CTP).
> > I am able to set up the linked server but when I execute a query then I
> > get
> > the following error
> >
> > The OLE DB provider "MSDAORA.1" for linked server "ORACLDB" reported an
> > error. Access denied.
> > Msg 7301, Level 16, State 2, Line 1
> > Cannot obtain the required interface ("IID_IDBCreateCommand") from OLE DB
> > provider "MSDAORA.1" for linked server "ORACLDB".
> >
> >
> > I am able to connect to this ORACLE base using SQL PLUS. I am also able to
> > extract data using SSIS.
> >
> > Thanks
> > Shri
>
>|||Don't use the Microsoft OLEDB Provider for Oracle, use the Oracle one
--
Dandy Weyn
[MCSE-MCSA-MCDBA-MCDST-MCT]
http://www.dandyman.net
Check my SQL Server Resource Pages at http://www.dandyman.net/sql
"Shri Rao" <ShriRao@.discussions.microsoft.com> wrote in message
news:8BC6A8EA-C372-442D-B793-B7C84DDCF751@.microsoft.com...
>I do have the Oracle client installed and I am able to connect using SQL+,
>I
> am also able to extract the data using SSIS.
> "Dandy Weyn [Dandyman]" wrote:
>> Install the Oracle Client tools should help.
>> You need to have Oracle Client installed
>> --
>> Dandy Weyn
>> [MCSE-MCSA-MCDBA-MCDST-MCT]
>> http://www.dandyman.net
>> Check my SQL Server Resource Pages at http://www.dandyman.net/sql
>> "Shri Rao" <Shri Rao@.discussions.microsoft.com> wrote in message
>> news:1E6D4028-A7B3-4995-B71B-83FBB8FFB4B5@.microsoft.com...
>> >I am trying to set a linked server for an Oracle base(9.2) to SQL Server
>> > 2005(June CTP).
>> > I am able to set up the linked server but when I execute a query then I
>> > get
>> > the following error
>> >
>> > The OLE DB provider "MSDAORA.1" for linked server "ORACLDB" reported an
>> > error. Access denied.
>> > Msg 7301, Level 16, State 2, Line 1
>> > Cannot obtain the required interface ("IID_IDBCreateCommand") from OLE
>> > DB
>> > provider "MSDAORA.1" for linked server "ORACLDB".
>> >
>> >
>> > I am able to connect to this ORACLE base using SQL PLUS. I am also able
>> > to
>> > extract data using SSIS.
>> >
>> > Thanks
>> > Shri
>>

server to Oracle

Hello Group:

I use a Linked Server to connect to a Oracle 8i base. Now I want to implement a distributed transaction using this Linked Server: Two-Phase Commit, but it doesn't work. I can send a "begin tran", followed by an "update" at the SQL 2000, but when I send a query to Linked Server, a simple "select", I get this error:
Server: Msg 7395, Level 16, State 2, Line 1
Unable to start a nested transaction for OLE DB provider 'MSDAORA'. A nested transaction was required because the XACT_ABORT option was set to OFF.
[OLE/DB provider returned message: Cannot start more transactions on this session.]
OLE DB error trace [OLE/DB Provider 'MSDAORA' ITransactionLocal::StartTransaction returned 0x8004d013: ISOLEVEL=4096].

I try the Oracle OLE DB Provider without sucess.

Can anyone help me ?

Thank you,

Aldair.Try sending simple queries to the oracle server & see what you get. Then gradually work up to nested transactions - keep it simple through each step of troubleshooting.

Also, do some research on the version of driver you are using - some are known to have bugs similar to what you describe.

Post back if problems,,

Cheers,

SG|||Well, because the Msg 7395 says "...XACT_ABORT..." is set to OFF, and a consult the BOL, I try set XACT_ABORT to ON. After this, I can do a distributed transaction. I send "begin tran" at SQL Srv, then send "update" at SQL Srv (OK), then I send an Update do Oracle, using Linked Server, and it works fine.
But, another problem, the linked server connection to Oracle hold locks for a long time, then another transaction that send a "insert", for instance, receive a time-out failure.

Can I force the linked server to Oracle to free locks ? How?

TIA,

Aldair.|||Well, because the Msg 7395 says "...XACT_ABORT..." is set to OFF, and a consult the BOL, I try set XACT_ABORT to ON. After this, I can do a distributed transaction. I send "begin tran" at SQL Srv, then send "update" at SQL Srv (OK), then I send an Update do Oracle, using Linked Server, and it works fine.
But, another problem, the linked server connection to Oracle hold locks for a long time, then another transaction that send a "insert", for instance, receive a time-out failure.

Can I force the linked server to Oracle to free locks ? How?

TIA,

Aldair.|||Hi

When you send a command to oracle, check the type of default locking that oracle uses - in SQL its read uncommitted for a transaction but oracle may be different. You can specify locking hints in your transactions BUT oracle may NOT recognise them, so you may need to customise the distributed transactions using oracle commands accordingly.

This type of problem requires slow, careful & methodical testing - dont rush it.

Post back if problems

Cheers

SG|||Hi.

Thank you for yours helps. I will try with care.

SYL.

Aldair.

Friday, February 24, 2012

server to Oracle using SQL Server 2005

server to Oracle using SQL Server 2005

server to Oracle using SQL Server 2005

server to Oracle

linking two datasets

Blog Archive

About Me