linked server problem at sql server while connecting to oracle dbms

Question

I have created a linked-server definition according to the article at :
http://www.ideaexcursion.com/2009/01/05/connecting-to-oracle-from-sql-server/

My aim is to transfer rows to tables at Oracle 11gR2.

After creating linked server, whenever I try to select a table using a query like :

SELECT *
FROM [192.168.1.188]..[ESIPARIS].[T_ERROR_LOG]

I get the error below :

Msg 7356, Level 16, State 1, Line 1
The OLE DB provider "OraOLEDB.Oracle" for linked server "192.168.1.188"
supplied inconsistent metadata for a column. The column "EVENT_OBJECT"
(compile-time ordinal 2) of object ""ESIPARIS"."T_ERROR_LOG"" was reported
to have a "LENGTH" of 50 at compile time and 100 at run time.

One more thing is that it duplicates field names whenever a select statment is prepared by "Sql Server Management Studio", some fields are duplicated as below :

SELECT [EVENT_DATE]
      ,[EVENT_DATE]
      ,[EVENT_DATE]
      ,[EVENT_DATE]
      ,[EVENT_OBJECT]
      ,[EVENT_OBJECT]
      ,[EVENT_OBJECT]
      ,[EVENT_OBJECT]
      ,[MESSAGE]
      ,[MESSAGE]
      ,[MESSAGE]
      ,[MESSAGE]
      ,[EVENT_ID]
  FROM [192.168.1.188]..[ESIPARIS].[T_ERROR_LOG]

I would be very happy to hear from you about any ideas, thank you for your concern,

Best Regards,

Kayhan YÜKSEL

Answer 1

There are a number of scenarios which might throw this error:

• your distributed query in SQL Server references a view with an underlying table in Oracle with a primary key column created in a certain way, Find out more
• there's a bug when the querying a view with numeric columns. Find out more
• it may be a problem with driver incompatibility, such as using the MS OleDB driver instead of the one Oracle provides.

If it isn't the driver one possible workaround is to use OPENQUERY. Otherwise. this support note contains general information on troubleshooting linked server and Oracle.

---

(This problem is a fairly generic one, so it turned out that the actual resolution was none of the things I suggested. I'm incorporating @kayhanyüksel's solution in the body of this response for the sake of completeness.)

Solved it with changes at listener and tnsnames. We are now able to connect from SQL Server to Oracle 11gR2 (running on 64 bit Red Hat Enterprise Linux 5.4 ) and vice versa. Documents followed are - Making a Connection from Oracle to SQL Server
- The Oracle Gateways documentation