sp_addlinkedserver connecting Oracle

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.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

sp_addlinkedserver connecting Oracle

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-04-28 : 08:08:04

webjagger writes "I have a problem to create a connection to Oracle with
EXEC sp_addlinkedserver
@server='ItsOracle'
,@srvproduct='Its Oracle'
,@provider='MSDAORA'
,@datasrc='ITS'
,@location=null
,@provstr=null
,@catalog=null

EXEC sp_addlinkedsrvlogin
@rmtsrvname='ItsOracle'
,@rmtuser='myUser'
,@rmtpassword = 'myPW'
,@useself=false
,@locallogin=null

These to statements work properly but now I do not know how to create my FROM clause if my user which is the owner of the table "mytab" is "mytabowner". If I do it like this "SELECT * FROM ITSOracle.ITS.mytabowner.mytab" it does not work.

Can you help me or is that to much an Oracle problem?"

amachanic
SQL Server MVP

169 Posts

Posted - 2005-04-28 : 10:12:37

I've never gotten four-part naming to work against an Oracle linked server (using the OraHome driver); instead, I've had to use OPENQUERY. Give it a try:

SELECT *
FROM OPENQUERY (ITSOracle, 'SELECT * FROM mytabowner.mytab')

---
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources