Linked Servers

[Dom]

I posted this at SqlServer-programming group, but got no responses.

We have two servers, one MS SQL Server, the other Oracle. Sometimes
I
need to get data from oracle in the MyDatabase catalog, bring it to
MSSql, and join it to other tables and proceed from there. I would
like to use LInked Servers instead.

I did the following:
exec sp_AddLinkedServer
'ORACLE'
, 'MyDatabase'
, 'MSDAORA'
, 'x.x.x.x'
Of course, "x.x.x.x" was actually the IP address of the server.
"ORACLE" appears in my Lilnked Servers list in SSMS, but I can't view
any of the tables because "The OLD EB Provider MSDAORA has not been
registered."
Am I doing the right thing in the SQL Statement?
If so, how do I register the provider?

[Erland Sommarskog]

Dom (do********@gmail.com) writes:

I posted this at SqlServer-programming group, but got no responses.

We have two servers, one MS SQL Server, the other Oracle. Sometimes I
need to get data from oracle in the MyDatabase catalog, bring it to
MSSql, and join it to other tables and proceed from there. I would like
to use LInked Servers instead.

I did the following:
exec sp_AddLinkedServer
'ORACLE'
, 'MyDatabase'
, 'MSDAORA'
, 'x.x.x.x'
Of course, "x.x.x.x" was actually the IP address of the server.
"ORACLE" appears in my Lilnked Servers list in SSMS, but I can't view
any of the tables because "The OLD EB Provider MSDAORA has not been
registered."

Funny, since MSDAORA is part of Windows, it should be there.

Then again, which version of Oracle do you use? MSDAORA is old and
dusty, and is good for Oracle 8, and maybe Oracle 9. For later versions,
you should definitely use Oracle's own provider.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Dom]

On Oct 31, 5:31*pm, Erland Sommarskog <[e-mail]:

Dom ([e-mail]) writes:

I posted this at SqlServer-programming group, but got no responses.

We have two servers, one MS SQL Server, the other Oracle. *Sometimes I
need to get data from oracle in the MyDatabase catalog, bring it to
MSSql, and join it to other tables and proceed from there. *I would like
to use LInked Servers instead.

I did the following:

exec sp_AddLinkedServer
* * * * 'ORACLE'
* * * * , 'MyDatabase'
* * * * , 'MSDAORA'
* * * * , 'x.x.x.x'

Of course, "x.x.x.x" was actually the IP address of the server.
"ORACLE" appears in my Lilnked Servers list in SSMS, but I can't view
any of the tables because "The OLD EB Provider MSDAORA has not been
registered."

Funny, since MSDAORA is part of Windows, it should be there.

Then again, which version of Oracle do you use? MSDAORA is old and
dusty, and is good for Oracle 8, and maybe Oracle 9. For later versions,
you should definitely use Oracle's own provider.

--
Erland Sommarskog, SQL Server MVP, [e-mail]

Links for SQL Server Books Online:
SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000:http://www.microsoft.com/sql/prodinf...ns/books.mspx- Hide quoted text -

- Show quoted text -

Thanks for the reply, Erland, but can you expand a little. I'm using
Oracle 10g. When I look at my list of providers, I see only seven of
them, most start with "MS" so I assume they are not Oracle's native
driver. What is the driver I should be using, and how do I register
it?

[Erland Sommarskog]

Dom (do********@gmail.com) writes:

Thanks for the reply, Erland, but can you expand a little. I'm using
Oracle 10g. When I look at my list of providers, I see only seven of
them, most start with "MS" so I assume they are not Oracle's native
driver. What is the driver I should be using, and how do I register
it?

If you are using Oracle 10g, you should not use MSDAORA. You need to
get hold of Oracle's OLE DB provider. As for how you do it, I have no
idea. I would kind of expect Oracle to have it available as a free download,
but who knows, maybe you have to pay for it.

I know at least one Oracle person who monitors this group, but you may
be better off asking in comp.databases.oracle about their provider. Or
just Google around...
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Geoff Muldoon]

In article <Xn********************@127.0.0.1>, Erland Sommarskog says...

Dom (do********@gmail.com) writes:

Thanks for the reply, Erland, but can you expand a little. I'm using
Oracle 10g. When I look at my list of providers, I see only seven of
them, most start with "MS" so I assume they are not Oracle's native
driver. What is the driver I should be using, and how do I register
it?

If you are using Oracle 10g, you should not use MSDAORA. You need to
get hold of Oracle's OLE DB provider. As for how you do it, I have no
idea. I would kind of expect Oracle to have it available as a free download,
but who knows, maybe you have to pay for it.

I know at least one Oracle person who monitors this group, but you may
be better off asking in comp.databases.oracle about their provider. Or
just Google around...

http://www.oracle.com/technology/sof..._db/index.html

GM