Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
| FAQ | Members List | Calendar | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
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...



Reply With Quote
  #2 (permalink)  
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





Reply With Quote
  #3 (permalink)  
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


Reply With Quote
  #4 (permalink)  
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?

Reply With Quote
  #5 (permalink)  
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...
Reply With Quote
  #6 (permalink)  
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: ].

Reply With Quote
  #7 (permalink)  
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.

Reply With Quote
  #8 (permalink)  
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..



Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 06:30 AM.


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