March 22, 2002 at 1:22 am
How do you set up a linked server against an Oracle database and how is the syntax to query it ??
Lars Bremberg
WM-data Public Partner
Sweden
Lars Bremberg
WM-data Public Partner
Sweden
March 22, 2002 at 9:56 am
Found this in BOL
. Use the Microsoft OLE DB Provider for Oracle
This example creates a linked server named LONDON Mktg that uses the Microsoft OLE DB Provider for Oracle and assumes that the SQL*Net alias for the Oracle database is MyServer.
USE master
GO
-- To use named parameters:
EXEC sp_addlinkedserver
@server = 'LONDON Mktg',
@srvproduct = 'Oracle',
@provider = 'MSDAORA',
@datasrc = 'MyServer'
GO
-- OR to use no named parameters:
USE master
GO
EXEC sp_addlinkedserver
'LONDON Mktg',
'Oracle',
'MSDAORA',
'MyServer'
GO
If that doesnt work you could also try using the OLEDB provider for ODBC with a system DSN.
I have a problem myself in that I am tying to use a visual foxpro database as a linked server. I have only been able to query the data the following way so far:
SELECT *
FROM OPENQUERY(Foxprolinkedserver, 'SELECT * FROM contact')
GO
although I think I should be able to simply supply a qualified name to access the tables in the linked server, I have had problems doing this. If anyone knows how to do this please post.Thanks
Hope this was useful.
March 22, 2002 at 10:28 am
OK to sort of answer my own question, I think that my problems in using a 4 part qualified nameing converntion to access the visual foxpro linked server is down to the OLEDB provider for ODBC not supporting this funcionality. You may get lucky with the Oracle provider and be able to issue a command such as:
SELECT * FROM OracleLinkedServer.databasename.dbo.Employees
I also found that you can miss out some parts of the 4 part name in certain circumstances and it fills them in for you.
eg
OracleLinkedServer.databasename..Employees
Don't know quite how it is doing this tho, best to be explicit i think.
March 22, 2002 at 10:46 am
ALso you run into many issues based on the version of the Oracle server and the client you are using. I usually for speed purposes though, once I get it setup properly, use openquery. I do this for various reason,
1) I can run a query on the remote server natively.
2) I get full bennifit of the remote servers indexes
3) I can limit the number of rows to come across the link
4) I use the remote resources for this
5) Using speific functions in Oracle to manipulate the data
These add lots to my overall access speeds. Otherwise I pull a copy of the table locally.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply