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

Answer 2

I had the same problem: The column ...... was reported to have a "LENGTH" of 50 at compile time and 100 at run time. and duplicate column names when selected. while i was trying to run a query in MS SQL from an ORACLE 11g database

I used the follownig type of query and it worked !

  DECLARE @TSQL varchar(8000)
  SELECT  @TSQL = 'SELECT * FROM OPENQUERY(MyLinkedServer,''SELECT * FROM TableName'')'
  EXEC (@TSQL)

where MyLinkedServer is the name of the linked server and TableName is the name of the table.

here you have the link to the article that helped me: http://support.microsoft.com/kb/314520

Answer 3

Old thread but it may be useful to someone. When I recently encountered this error, using as provider the MS OleDB driver instead of the Oracle OleDB provider solved the problem.

Answer 4

I have the same issue with 11g client but it was disappeared with client version 12 which works for me is using OPENQUERY and to_char with the field that makes problem.

I confirm that SQL management studio (no matter what version) gives many duplicated field. The only installing of last driver version we can have consistent queries. I hope it can be useful for you!