473,325 Members | 2,860 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,325 software developers and data experts.

Linked Server Error Between SQL Server and Oracle

I have used Linked Servers to hook up to an Oracle 9i database in SQL
Server 2000. I can see all the Tables and Views when I look in the
linked server section. I can also run the following SQL statement :

Select *
from Orcldb..SYSTEM.CrossTabSaveSites

and get a return result. The problem I realize is that some of the
tables are going to be quite larger and I can't wait for SQL Server to
download all the data and parse it itself, so I tried to use a
passthrough query like so :

SELECT * FROM OPENQUERY(OrclDB, 'SELECT * FROM
SYSTEM.CrossTabSaveSites')

Unfortunately this gives me the error message :

Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB
provider 'MSDAORA'.
[OLE/DB provider returned message: ORA-00942: table or view does not
exist
]

There is also the issue of speed. It takes about 15 seconds to come
back to me to tell me that the table/view doesn't exist, but sometimes
it comes back immediately. Is this something with the user
permissions in Oracle? (Don't really know much about Oracle) It just
seems like a very long time to wait just to let me know that the item
doesn't exist.

Any help would be greatly appreciated,

-Jevon Thurlow
Jul 20 '05 #1
1 9531

"Jevon" <je**********@hotmail.com> wrote in message
news:34**************************@posting.google.c om...
I have used Linked Servers to hook up to an Oracle 9i database in SQL
Server 2000. I can see all the Tables and Views when I look in the
linked server section. I can also run the following SQL statement :

Select *
from Orcldb..SYSTEM.CrossTabSaveSites

and get a return result. The problem I realize is that some of the
tables are going to be quite larger and I can't wait for SQL Server to
download all the data and parse it itself, so I tried to use a
passthrough query like so :

SELECT * FROM OPENQUERY(OrclDB, 'SELECT * FROM
SYSTEM.CrossTabSaveSites')

Unfortunately this gives me the error message :

Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB
provider 'MSDAORA'.
[OLE/DB provider returned message: ORA-00942: table or view does not
exist
]

There is also the issue of speed. It takes about 15 seconds to come
back to me to tell me that the table/view doesn't exist, but sometimes
it comes back immediately. Is this something with the user
permissions in Oracle? (Don't really know much about Oracle) It just
seems like a very long time to wait just to let me know that the item
doesn't exist.

Any help would be greatly appreciated,

-Jevon Thurlow


I can't tell you anything about the performance issue, but the ORA-00942
error is probably because Oracle is case-sensitive, and object names are
usually in upper case:

http://support.microsoft.com/default...b;EN-US;240340
http://support.microsoft.com/default...b;EN-US;280106

Simon
Jul 20 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: David Gray | last post by:
Hello all, Having problems connecting to an Oracle 9i database from within SQL/Server 2000 using the Security/Linked Servers feature. Server1 (SQL/Server) ----------- Windows Server 2003,...
14
by: Sudhesh Nayak | last post by:
Hi, I have an Oracle (8.1) & a SQL Server 2000 database with Production data. There are situations when I need data from both the databases. My first choice was to link Oracle to SQL and run DTS...
2
by: Pardhasaradhy | last post by:
Hello, I have a linked server to oracle 7.1 from SQL 2000. when I try to execute simple select statement which returns one row of data using openquery is not fetching the data. After 30 minutes...
3
by: wotan | last post by:
We have a linked server, which worked fine, but we have just changed the collation sequence, in SQL-Server, from SQL_Latin1_General_CP1_CI_AS to Latin1_General_CI_AS. Now trying to use the...
11
by: Ellen K | last post by:
Hi all, I set up our Oracle Financials as a linked server to one of my SQL Server boxes. On running a test query, I got the following error message: OLE DB provider 'MSDAORA' supplied...
2
by: tim.pascoe | last post by:
I'm currently trying to establish a linked server to an Oracle database. Setup: Connecting to 8x version of Oracle Using 9i client tools (Net Manager) SQL-Server 2000 Windows 2000 I...
4
by: arichie | last post by:
Can anyone point me where can I find a good architectural document on how the Linked server works in SQL Server. I am not asking about how to use Linked server, I am interested in know how it is...
3
by: Chris | last post by:
I need to link some data from SQL Server 2005 with Oracle 10 data. One way is to link Oracle server to SQL Server and use ROWSOURCE for retrieving data. What other ways for joing data from...
0
by: martin leer | last post by:
Hi all. I got the following error: OLE DB provider "MSDAORA" for linked server "infonet" returned message "Oracle client and networking components were not found. These components are...
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...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
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: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
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)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
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: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

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.