SQL Server Linked Server Problem 1

[nh]

SQL Linked Server - OLE DB Fault - connecting to Oracle
Hi,

I have a problem with an SQL Server Linked Server and OLE DB.

WinNT (SP6)
SQL Server 7 SP3 (7.00.961)
MDAC Version 2.6 RTM
Oracle Client 8.1.7

Oracle DB 8.0.6 (running on a Unix system)

I cannot read any data using the linked server from the Oracle Tables.

Using Query Analyser ...Example Program......

EXEC sp_addlinkedserver 'scs', 'Oracle', 'MSDAORA', 'scs01p' -- TNS Name is 'scs01p'
GO
EXEC sp_addlinkedsrvlogin 'scs', FALSE, NULL, 'digitron', 'digitron'
GO
SELECT * FROM scs..DIGITRON.NEILTEST1

This produces the following error........

(1 row(s) affected)
(1 row(s) affected)
Server added.
(1 row(s) affected)
(0 row(s) affected)
(1 row(s) affected)

Server: Msg 7370, Level 16, State 2, Line 1
One or more properties could not be set on the query for OLE DB provider 'MSDAORA'. The provider could not support a required property.
[OLE/DB provider returned message: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.]


Yet if I execute the following SP from QA.....
EXEC sp_tables_ex scs

I get a full system catalog / owner / table list!
....So I assume that the Linked Server is all OK.

I have also tried using the MS ODBC Driver for Oracle, this produces the same results.

I can access the data from the remote Oracle DB using the SQL*PLUS & ODBC Test Tools provided with Oracle.

Anyone any ideas?

Regards

Neil

 

[tlbroadbent]

Have you tried using the OpenQuery function?

SELECT * FROM OPENQUERY ( scs,
'Select * From DIGITRON.NEILTEST1' )

Sometimes OpenQuery handles linked server queries better than a query using the four-part table name. It's worth a try. Terry

http://members.home.net/tlbroadbent/index.htm

 

[nh]

Thanks for your reply Terry,
I had already tried using OpenQuery with no success.

Anyway I have SOLVED my problem!!!

I have changed the remote query timeout option value
in SQL Server from 200 (default) to 0 (0 = Infite wait)
This solved the problem!

I don't understand why 200 would not work (I guess a bug in SQL Server?)

If the error message had only pointed to a timeout issue 5 days ago, (....and n * ServicePack Upgrades!)

Anyway it works now.

Regards

Neil