Please help with linked servers?

[Marcha]

I'm trying to link SQL server 7 and oracle with no success, all I get is that mistique error: 7399 'OLE DB Provider 'MSDAORA' Reported an error' without any explanations (when tried to query the linked server the error was a bit more specific: "OLE DB provider 'MSDAORA' reported an error. [OLE/DB provider returned message: An Oracle Error occured, but message could not be determined]&quot. I searched through all msdn but haven't found anything useful. Please help!
The problem is that SQL Server sets up a linked one, however I cannot connect to it (I also tried to link two sql servers and that works fine), for example:
sp_addlinkedserver
@server = 'Linky',
@srvproduct = 'Oracle',
@provider = 'MSDAORA',
@datasrc = 'SERVER*'
SERVER* is the SQL*NET alias.
Then as explained in the MSDN I create valid login using sp_addsrvlogin (also tried all this from console, but to no avail)
And VOILA nothing works (
I'm desperate, please someone tell me what i'm doing wrong.
P.S "tnsping SERVER*" from command line works fine.
Marcha

 

[TomasDill]

Hi!

I had the same problem with Linked Servers to Access database. However, when I created linked servers in the Enterprise Manager - all Ok. Try to create them in Enterprise manager, if all Ok, than you just need to play around parameters for sp_addlinkedserver and sp_addsrvlogin a little bit more. The best way to check that they work is to try to get list of tables in linked server in Enterprise manager. If it gives error, probably something wrong with linking. When tables shown ok, that error mentioned by you related to the query you send to Oraccle database.



Vlad Grynchyshyn
vgryn@softserve.lviv.ua

http://www.softserve.lviv.ua

The professional level of programmer could be determined by level of stupidity of his/her bugs

 

[Marcha]

As I said I tried both (from the Enterprise Manager Console & Query Analizer) the problem seems to be the connection/configuration related (becouse when I tried inputing invalid data it also added the linked server and the error this time was the same, however the dummy linked server pointed to nowhere). I find this very strange. Maybe someone who already have done this (linked Oracle and SQL) could help by saying what/how exactly I should configure the Oracle or SQL server. Maybe I'm missing something obvious. If someone can, please help.
Thanks for input and your valuable time!
Marcha