Question using SP_ADDLINKEDSERVER

[EVH316]

Hi To All,

I'm having problem when accessing my Oracle table in SQL Server, I successfully created and OracleLinkedSer ver but when I tried to select the oracle table via linked server I encountered the error message OLE DB provider 'OracleLinkedSe rver' 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.

[ck9663]

Could you post your query?

--- CK

[EVH316]

Hi CK,

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

EXEC sp_addlinkedser ver @server = 'OracleLinkedSe rver', @srvproduct = 'Oracle', @provider = 'MSDAORA', @datasrc = 'KTPDEV';

EXEC sp_addlinkedsrv login 'OracleLinkedSe rver', FALSE, 'sa', 'dwa', 'dwa';

SELECT * from OracleLinkedSer ver..dwa.dwa_te mp_div_stg;

Thanks...

[ck9663]

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

[EVH316]

Hi CK,

Thanks for the reply this the actual message:
Server: Msg 7314, Level 16, State 1, Line 1
OLE DB provider 'OracleLinkedSe rver' does not contain table '"dwa"."dwa_tem p_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='O racleLinkedServ er', 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_st g. It seems that the owner cannot see its own table dwa_temp_div_st g. 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

[ck9663]

Have you read this ?

--- CK

[EVH316]

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