Linked server issue on sql server when connecting to oracle dbms

Question

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

My goal is to wrap rows in tables in Oracle 11gR2.

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

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

I get the error message 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.

Another thing is that it duplicates the field names whenever the selected item is created by Sql Server Management Studio, some fields are duplicated, as shown 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 glad to hear from you any ideas, thanks for your concern,

Regards,

Kayhan YÜKSEL

Answer 1

, :

• SQL Server Oracle ,
• .
• , MS OleDB, Oracle.

, OPENQUERY. . Oracle.

---

( , , , . @kayhanyüksel .)

tnsnames. SQL Server Oracle 11gR2 ( 64- Red Hat Enterprise Linux 5.4) .  - Oracle SQL Server
 - Oracle Gateways

Answer 2

: ...... "LENGTH" 50 100 . . MS SQL ORACLE 11g

follownig, !

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

MyLinkedServer - TableName - .

, : http://support.microsoft.com/kb/314520

Answer 3

, - . , , MS OleDB Oracle OleDB .

Answer 4

I have the same problem with the 11g client, but it has disappeared from the client version 12, which works for me, use OPENQUERY, and to_charwith the field, which poses a problem.

I confirm that SQL management studio(no matter which version) gives a lot of duplicate fields. A single installation of the latest driver version may have consistent requests. Hope this can be helpful for you!