Question using SP_ADDLINKEDSERVER

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.

re: Question using SP_ADDLINKEDSERVER

Could you post your query?

--- CK

re: Question using SP_ADDLINKEDSERVER

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...

re: Question using SP_ADDLINKEDSERVER

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

re: Question using SP_ADDLINKEDSERVER

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

re: Question using SP_ADDLINKEDSERVER

Have you read this ?

--- CK

re: Question using SP_ADDLINKEDSERVER

Thanks CK for the info but I still got the same error message...