ODBC / Oracle Linked Server Problem

I have a SQL server that I am trying to link to a number of Oracle environments. After much tuning, we managed to achieve this although the four-part naming was not possible and we had to use Openquery and run pass throughs.

Nothing in our configuration has changed and SQL Server is no longer able connect to the linked databases. The Oracle client on the PC is fine and is able tnsping any of the remote databases. I am also able to create ODBC connections to the remote databases on the SQL box that are fine.

Using a datalink in DTS, I can connect to the remote databases. This suggests to me that there is something wrong within the actual database links. I have set them up using the working ODBC DSN's on the SQL box.

If I try and run a query against them in Query Analyser, I get the following error message :

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' reported an error.
[OLE/DB provider returned message: ORA-12154: TNS:could not resolve service name
]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80004005: ].

If I click on the tables icon in EM to view the remote catalogues I get the following error :

Error 7399: OLE DB provider 'MSDORA' reported an error.
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80004005: ].

Any help that could be give on this would be greatly appreciated.Can you post the script(s) that you used to create the linked servers on your SQL Server?

Also, I think it would be helpful if you could post the content of the tnsname.ora file.

regards,

hmscott|||Hi, it appears the problem lies somewhere in the Oracle client on the server or the NT build. We have successfully managed to get the links up and alive by bouncing the server once a day, stopping and restrating the SQL Services and refreshing the login details.

We are currently investigating as to whether it not it could be related to the connections created by Terminal Services.


Originally posted by hmscott
Can you post the script(s) that you used to create the linked servers on your SQL Server?

Also, I think it would be helpful if you could post the content of the tnsname.ora file.

regards,

hmscott|||I'm ware of the errmsg "Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' reported an error.
[OLE/DB provider returned message: ORA-12154: TNS:could not resolve service name
] "
I think the problem is that u haven't configure sql*net correctly.
See the tnsnames.ora in %ORACLE_HOME%/network/admin.

Obtaining a value from Openquery?

Hi all,

I have an Informix Dynamic Server linked within my MS SQL 7 server. What I want to achieve is to be able to obtain a value from the informix table and then to use this value to update the MS SQL server table. I am doing this within a trigger on SQL Server. I am not doing this from infromix as I cant get informix to see the SQL Server.

My problem is that I dont know how to assign the query result to a variable so I can use it in my Update. Can anyone help me with my syntax?? Below is my variable settings and query within the Insert trigger...(Not sure if its correct)

DECLARE @.TSQL VARCHAR(100)
DECLARE @.NAMEID VARCHAR(10)

SET @.NAMEID = (Select Inserted.NameID from Inserted)

SET @.TSQL = 'SELECT * FROM OPENQUERY(AUTHTEST, ''Select nar_num from aunrmast where dpid = '' + @.NAMEID + '')'

EXEC (@.TSQL)

How do I set a variable with the nar_num value that I get back from the informix server. Any Help would be great.

Thanks
Anthonyyou would need to fill in the data type for nar_num but give this a try:

DECLARE @.TSQL VARCHAR(100)
, @.NAMEID VARCHAR(10)

create table #tmp(nar_num <data type>)

select @.NAMEID = min(NameID) from Inserted
while (@.NAMEID is not null) begin
SET @.TSQL = 'SELECT * FROM OPENQUERY(AUTHTEST, ''Select nar_num from aunrmast where dpid = '' + @.NAMEID + '')'

truncate table #tmp
insert into #tmp
EXEC (@.TSQL)

select @.NAMEID = min(NameID) from Inserted where nameid > @.NAMEID
end

Please note that I changed things a bit to handle more than one one record.