By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,766 Members | 1,290 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,766 IT Pros & Developers. It's quick & easy.

Linked Servers

P: n/a
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?
Oct 31 '08 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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

Oct 31 '08 #2

P: n/a
Dom
On Oct 31, 5:31*pm, Erland Sommarskog <esq...@sommarskog.sewrote:
Dom (dolivas...@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, esq...@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...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?
Nov 3 '08 #3

P: n/a
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

Nov 3 '08 #4

P: n/a
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
Nov 3 '08 #5

This discussion thread is closed

Replies have been disabled for this discussion.