Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
pwvailla
Starting Member
31 Posts |
 Posted - 2010-11-18 : 15:31:45
|
Hi,I have the need to do a SELECT against an Oracle table. I have defined the linked server and tested it with the IMPORT wizard such that I can do a full table import.However, I want to do conditional selects against a limited set of fields involving this table. How do I do that? I know to "use a four-part name in the form linked_server_name.catalog.schema.object_name in Transact-SQL statements" Does this convention still apply to Oracle databases?Also, I see a reference for using an Oracle database:in an Oracle linked server must be referenced by using a four-part name of the form OracleLinkedServerName..OwnerUserName.TableName.For example, the following SELECT statement references the table SALES owned by the Oracle user MARY in the server mapped by the OrclDB linked server:SELECT * FROM OrclDB..MARY.SALESStill, with all this information, I am unable to make anything work. How do you deal with passwords used by Oracle?My linked server is named ERP and the table name is OPS.PO_VENDORS, shouldn't this work?select * from ERP..OPS.PO_VENDORSThanks  |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2010-11-18 : 17:20:16
|
| I've not done this myself, but is Oracle case sensative with its object names? |
 |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2010-11-19 : 03:21:50
|
| "I've not done this myself, but is Oracle case sensative with its object names?"In the database i'm using "not in relation to the object names"...but yes re the data values....just like SQL can be case sensitive. |
|
|
|
pwvailla
Starting Member
31 Posts |
Posted - 2010-11-19 : 13:23:05
|
Brett,The IM organization set up the linked servers as I have no privileges. I do not see anything like:sp_addlinkedsrvlogin 'OrclDB', false, 'Joe', 'OrclUsr', 'OrclPwd' which is referenced in the SQL Server 2005 help text. Should I? I want to be able to issues SELECTS to the data like:SELECT * FROM OrclDB..MARY.SALESPaul quote:
Originally posted by X002548
well..along with the linked server did you add a remote login?Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxWant to help yourself?http://msdn.microsoft.com/en-us/library/ms130214.aspxhttp://weblogs.sqlteam.com/brettk/http://brettkaiser.blogspot.com/
|
|
|
|
Yalini2212
Starting Member
12 Posts |
Posted - 2010-11-20 : 08:07:14
|
| Usually you need admin rights to use linked servers. You can use a dts package or (SSIS) to connect to oracle and from there u can pull the data to ur local table. from local, u can query the database. |
|
|
|
|
|
|
|
|
Accessing Oracle data with SQL Server 2005