473,320 Members | 1,930 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Question using SP_ADDLINKEDSERVER

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
6 7405
ck9663
2,878 Expert 2GB
Could you post your query?

--- CK
May 18 '09 #2
EVH316
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
2,878 Expert 2GB
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
EVH316
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
2,878 Expert 2GB
Have you read this ?

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

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

Similar topics

3
by: hitendra15 | last post by:
Can it be possible to create trigger/procedure in following case 2 server server A and server B A has db1 db1 has tblA B has db2 db2 has tblB can it be possible to create trigger on server...
0
by: Bill J. | last post by:
I have to update a PostgreSQL linked server through MSSQL2K. I first configured the connection with ODBC as follows and I can do queries with no problem: EXEC sp_droplinkedsrvlogin @rmtsrvname...
0
by: Bill J. | last post by:
I have to update a PostgreSQL linked server through MSSQL2K. I first configured the connection with ODBC as follows and I can do queries with no problem: EXEC sp_droplinkedsrvlogin @rmtsrvname...
11
by: Mark Yudkin | last post by:
The documentation is unclear (at least to me) on the permissibility of accessing DB2 (8.1.5) concurrently on and from Windows 2000 / XP / 2003, with separate transactions scope, from separate...
5
by: xchong.zhou | last post by:
I have a question when I work on Linkedserver The Linkedserver name is ,the Datebase name is Newexec,the Table name is Customers_CoypTest The SQLScript is below: Update ...
1
by: Sandy | last post by:
Hi, I want to write a query which reads data from a DataBase D1.table1 which is on some other machine and put it in DataBase D2.table2 The problem is 'How do i specify the IPAddress and the...
1
by: tim.pascoe | last post by:
I've been trying to work with Linked Servers for the first time, with mixed success. My problem right now is in trying to write a View using a linked server. The linked server is another...
4
by: Jchick | last post by:
This outta be an easy one for someone. I have the following code in my trigger. I am trying to connect to a DSN that is pointing to an Access database (.mdb). However, I am not sure how to actually...
56
by: spibou | last post by:
In the statement "a *= expression" is expression assumed to be parenthesized ? For example if I write "a *= b+c" is this the same as "a = a * (b+c)" or "a = a * b+c" ?
3
by: justin_ltg | last post by:
I have an access db that contains lots of data. I have a software package (want it) that will email out commission statements from the data that is in access. The catch? The data has to be in...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.