467,149 Members | 1,327 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,149 developers. It's quick & easy.

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?
Oct 31 '08 #1
  • viewed: 4298
Share:
4 Replies
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
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
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
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.

Similar topics

3 posts views Thread by Raghuraman | last post: by
8 posts views Thread by Ootyguy | last post: by
3 posts views Thread by RandyMartin | last post: by
reply views Thread by Abyss7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.