Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
| Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 2nd, 2006, 08:27 PM
Registered User
 
Join Date: Mar 2006
Location: , Ca, .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default Executing a query from SQL server to Oracle DB

I'm having trouble querying from my SQL server 2000 db on my local machine to a remote Oracle server 9i.

I can get the server link setup with both MS OLE DB and the Oracle OLE DB drivers and can get a list of tables.

EXEC sp_addlinkedserver
   @server = 'DB',
   @srvproduct = 'Microsoft OLE DB Provider for Oracle',
   @provider = 'MSDAORA',
   @datasrc = 'servername',
GO

EXEC sp_addlinkedsrvlogin
   @rmtsrvname = 'DB',
   @useself = 'false',
   @rmtuser = 'login',
   @rmtpassword = 'password'
GO

EXEC sp_tables_ex DB
GO

and viola, I get tables upon tables...:D


BUT when I do something simple like this:

SELECT * FROM DB...table where item = something or

SELECT * FROM DB..user.table where item = something


I get this with either OLE DB driver:

Server: Msg 7314, Level 16, State 1, Line 1
OLE DB provider 'DB' does not contain table 'table'. 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='DB', TableName='table'].

The tables do exist and the login has read-only rights to all tables... I'm totally stumped because the DB genius here sounded extremely relieved when I go the table listing like everything was going to be gravy from there.. any ideas would be amazingly appreciated... am I trying to query wrong? Should I be using some sort of db function or procedure to send the queries to the Oracle DB to get a result on the SQL server. I'm doing some testing for use in a Stored Procedure...



 
Old March 3rd, 2006, 12:59 PM
Friend of Wrox
Points: 2,101, Level: 18
Points: 2,101, Level: 18 Points: 2,101, Level: 18 Points: 2,101, Level: 18
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2003
Location: , , .
Posts: 599
Thanks: 6
Thanked 3 Times in 3 Posts
Default

Hi,

I don't have the exact answer but I did find some interesting links that might apply to your situation. Of course, you probably already know that you can connect to the server and the problem is most likely in how you fashion the query. Anyway, here are some examples of how to query linked servers. Hope this helps. Also, if you find the answer please post it.

Thanks,
Richard


http://www.codeproject.com/cs/databa...ed_Servers.asp

http://msdn.microsoft.com/library/de...uted_query.asp

http://www.devx.com/vb2themax/Tip/18623

http://www.sql-server-performance.com/linked_server.asp





 
Old March 3rd, 2006, 04:16 PM
Registered User
 
Join Date: Mar 2006
Location: , Ca, .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks Rich,
     Actually, about an hour later I found a post to use openquery through the MSDAORA provider and was able to query the DB properly. But, trying it on my dev box, I am receiving a 7399 error saying the oracle client hasn't been installed, when I am using the msdaora provider.. still stumped, but glad I can query on my personal test machine... : P


 
Old March 3rd, 2006, 04:26 PM
Friend of Wrox
Points: 2,101, Level: 18
Points: 2,101, Level: 18 Points: 2,101, Level: 18 Points: 2,101, Level: 18
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2003
Location: , , .
Posts: 599
Thanks: 6
Thanked 3 Times in 3 Posts
Default

Sounds like progress. So the OPENQUERY works?! Did you have the Oracle client installed on your personal test machine?

 
Old March 3rd, 2006, 04:36 PM
Registered User
 
Join Date: Mar 2006
Location: , Ca, .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Yes, *much* progress. The only thing keeping me from getting this into QA is knowing what to install on the QA machines for the link to function correctly.

I have so much crap installed on this machine, I don't know what's not allowing me to make the connection with MSDAORA, since it is a MS provider. I do have the oracle 10g dev suite installed on my machine.

I tried installing the Oracle provider for OLE DB on a dev machine(win2k3 server) that I have access to, but still got the error. I even setup the net config. I tried to switch to the Oracle provider, but now the enterprise manager hangs when I try to view the server. Not good...
 
Old March 3rd, 2006, 06:05 PM
Registered User
 
Join Date: Mar 2006
Location: , Ca, .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Server: Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 13
OLE DB provider 'MSDAORA' reported an error.
[OLE/DB provider returned message: Oracle client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 or later client software installation.

Provider is unable to function until these components are installed.]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80004005: ].

 
Old March 3rd, 2006, 07:45 PM
Friend of Wrox
Points: 2,101, Level: 18
Points: 2,101, Level: 18 Points: 2,101, Level: 18 Points: 2,101, Level: 18
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2003
Location: , , .
Posts: 599
Thanks: 6
Thanked 3 Times in 3 Posts
Default

Hmmmm.. You said you installed them right? Is your test box a server too? Could just need to uninstall and reinstall the client on the server. I don't know really. Just fishing. Are you running these tests in QA while logged on locally to both boxes? Sorry if this is a distraction to you since I don't really have THE answer.

 
Old March 24th, 2006, 08:15 AM
Registered User
 
Join Date: Mar 2006
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

On the Error:
OLE DB provider 'MSDAORA' reported an error.
[OLE/DB provider returned message: Oracle client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 or later client software installation.

I got the error (using client 10g), and was finally able to fix it by changing the following registry keys to the values indicated:

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI]
"OracleXaLib"="oraclient10.dll"
"OracleSqlLib"="orasql10.dll"
"OracleOciLib"="oci.dll"

These were set to some old values (probably for Oracle client 8 which was there on my machine earlier).

Hope this helps..







Similar Threads
Thread Thread Starter Forum Replies Last Post
Executing an SQL query and using it's result Andrew.Berry ASP.NET 2.0 Professional 5 April 14th, 2008 08:25 AM
Looking for a SQL Query Builder for ASP.Net/Oracle rashid_masud SQL Language 0 March 9th, 2006 06:37 AM
access db to sql server db mikersantiago Classic ASP Basics 4 November 16th, 2004 03:33 AM
Anyone fluent in Oracle and SQL Server? evaldesc Oracle 0 July 13th, 2004 11:09 AM
Query to be run against both SQL and Oracle NK Oracle 2 April 26th, 2004 05:28 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.