MS SQL Server - MSSQL 2005 Can't Query Linked Oracle Server

Author

MSSQL 2005 Can't Query Linked Oracle Server

mike21

2006-04-07, 8:23 pm

Hello All:

I am trying to create a server link between MSSQL 2005 and Oracle 9.2.0.7.
I can successfully execute the sp_addlinkedserver
and sp_addlinkedsrvlogin
s function but when I attempt to execute a query
against the Oracle server such as:

SELECT * FROM OPENQUERY(SD03, 'SELECT * FROM V$VERSION')

I receive the following error:

Msg 7403, Level 16, State 1, Line 1
The OLE DB provider " MSDAORA" has not been registered.

I then go to the C:\Program Files\Common Files\System\Ole DB folder and
execute the following:

regsvr32 msdaora.dll

which returns success and rerun the query, the error persists. I ran
Component Checker to confirm the MDAC is 2.82.

What do I need to execute queries against the Oracle server from MSSQL 2005.

Thanks,

David Browne

2006-04-07, 8:23 pm

"mike21" <rg6584@comcast.net> wrote in message
news:OQptDBpWGHA.3760@TK2MSFTNGP02.phx.gbl...
> Hello All:
>
> I am trying to create a server link between MSSQL 2005 and Oracle 9.2.0.7.
> I can successfully execute the sp_addlinkedserver
> and sp_addlinkedsrvlogin
s function but when I attempt to execute a query
> against the Oracle server such as:
>
> SELECT * FROM OPENQUERY(SD03, 'SELECT * FROM V$VERSION')
>
> I receive the following error:
>
> Msg 7403, Level 16, State 1, Line 1
> The OLE DB provider " MSDAORA" has not been registered.
>
> I then go to the C:\Program Files\Common Files\System\Ole DB folder and
> execute the following:
>
> regsvr32 msdaora.dll
>
> which returns success and rerun the query, the error persists. I ran
> Component Checker to confirm the MDAC is 2.82.
>
> What do I need to execute queries against the Oracle server from MSSQL
> 2005.
>

You need to install the Oracle Client software and reboot. Also you should
use the OleDB provider from Oracle, since the MS provider was not kept
up-to-date with later versions of Oracle.

David

> Thanks,
>
>

mike21

2006-04-07, 8:23 pm

Hello David:

I have the Oracle 9i client installed and tested successfully against the
Oracle server. I rebooted the MSSQL 2005 server, executed the same query
and received the same error:

Msg 7403, Level 16, State 1, Line 1
The OLE DB provider " MSDAORA" has not been registered.

Would I be better off trying an ODBC connection? I thought the OLE
connection would be best for migration of Oracle data to MSSQL 2005 server.

Thanks for your speedy response.

Mike

"David Browne" <davidbaxterbrowne no potted meat@hotmail.com> wrote in
message news:ui$irDpWGHA.4212@TK2MSFTNGP02.phx.gbl...
>
> "mike21" <rg6584@comcast.net> wrote in message
> news:OQptDBpWGHA.3760@TK2MSFTNGP02.phx.gbl...
>
> You need to install the Oracle Client software and reboot. Also you
> should use the OleDB provider from Oracle, since the MS provider was not
> kept up-to-date with later versions of Oracle.
>
> David
>
>

David Browne

2006-04-07, 8:23 pm

"mike21" <rg6584@comcast.net> wrote in message
news:ej9NELpWGHA.3908@TK2MSFTNGP03.phx.gbl...
> Hello David:
>
> I have the Oracle 9i client installed and tested successfully against the
> Oracle server. I rebooted the MSSQL 2005 server, executed the same query
> and received the same error:
>
> Msg 7403, Level 16, State 1, Line 1
> The OLE DB provider " MSDAORA" has not been registered.
>
> Would I be better off trying an ODBC connection? I thought the OLE
> connection would be best for migration of Oracle data to MSSQL 2005
> server.
>

No. OleDB is required for linked server. Try Oracle's OleDB provider.

David

Sponsored Links