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

Question using SP_ADDLINKEDSERVER

P: 26
Hi To All,

I'm having problem when accessing my Oracle table in SQL Server, I successfully created and OracleLinkedServer but when I tried to select the oracle table via linked server I encountered the error message OLE DB provider 'OracleLinkedServer' does not contain table '"[oracle schema]"."[tablename]"' Actually the owner of the table is under the schema that I mentioned when creating my linked server.

Thanks in advance for all your inputs.
May 18 '09 #1
Share this Question
Share on Google+
6 Replies


ck9663
Expert 2.5K+
P: 2,878
Could you post your query?

--- CK
May 18 '09 #2

P: 26
Hi CK,

Here's my exact query and I inclulde also how did I create my link server:

EXEC sp_addlinkedserver @server = 'OracleLinkedServer', @srvproduct = 'Oracle', @provider = 'MSDAORA', @datasrc = 'KTPDEV';

EXEC sp_addlinkedsrvlogin 'OracleLinkedServer', FALSE, 'sa', 'dwa', 'dwa';

SELECT * from OracleLinkedServer..dwa.dwa_temp_div_stg;

Thanks...
May 19 '09 #3

ck9663
Expert 2.5K+
P: 2,878
What's the actual error that showed up? Coz I think you have the two dots (..) misplaced. It's usually reserve to identify the default user as the owner of the object, not the schema/database.


--- CK
May 20 '09 #4

P: 26
Hi CK,

Thanks for the reply this the actual message:
Server: Msg 7314, Level 16, State 1, Line 1
OLE DB provider 'OracleLinkedServer' does not contain table '"dwa"."dwa_temp_div_stg"'. The table either does not exist or the current user does not have permissions on that table.
OLE DB error trace [Non-interface error: OLE DB provider does not contain the table: ProviderName='OracleLinkedServer', TableName='"dwa"."dwa_temp_div_stg"'].

My oracle schema (at the same time my oracle admin user) "dwa" is the owner of the table dwa_temp_div_stg. It seems that the owner cannot see its own table dwa_temp_div_stg. How can I check if the link server that I created can able to connect to my oracle database? Or did I create my link server correctly? Because I tried selecting other tables and I end up the same error message.

Thanks
May 21 '09 #5

ck9663
Expert 2.5K+
P: 2,878
Have you read this ?

--- CK
May 21 '09 #6

P: 26
Thanks CK for the info but I still got the same error message...
May 21 '09 #7

Post your reply

Sign in to post your reply or Sign up for a free account.