Linked Server Throws Metadata Error

Charles M 1 Reputation point
2020-09-17T12:21:59.363+00:00

I have a function in SQL Server 2019 that copies data from a local table to an Oracle 11.2 table via Linked Server, using Oracle 19.3 client. Recently, a new column was added to the Oracle table via an alter table statement, so I've updated the insert function to include data for this new column.

Now, whenever I modify the function, it will execute successfully one time and one time only. If I purge the query cache, it also executes once and once only. ALL subsequent execution attempts after the first time throw this error:

Msg 7353, Level 16, State 1, Procedure COD.Interface_Upload, Line 29 [Batch Start Line 20] The OLE DB provider "OraOLEDB.Oracle" for linked server "ORADEV-LINK" supplied inconsistent metadata. An extra column was supplied during execution that was not found at compile time.

The only change to this procedure from the previous, working, version is adding the DATE_SUBMITTED field and the "select min([Sample_date]) ..." part to supply that column's data.

Everything I can find says that if you're getting metadata errors, go from using standard queries to the OPENQUERY syntax, which is what I'm using.

The function is:

ALTER Procedure [COD].[Interface_Upload]
  As
    SET NOCOUNT ON;

    INSERT OPENQUERY([ORADEV-LINK], 'SELECT ACCOUNT_NUMBER, FROM_DATE, TO_DATE, 
            COD_TO_BOD_FACTOR, CONSUMPTION, SAMPLE_DATE, COD_CONTENT, SS_CONTENT,
            DATE_SUBMITTED from cbd.WS_CBD_PACS')
        Select [Account_Number]
            ,[From_Date]        ,[To_Date]
            ,[Factor]           ,[Consumption]
            ,[Sample_date]      ,[COD]
            ,[SS]
            , ( select min([Sample_date]) 
                from dbo.ORA_Interface 
                where Account_Number = p.Account_Number )
        from dbo.ORA_Interface p ;

    Return @@rowcount;
GO

The Oracle table definition that I'm trying to insert into is:

                         Null?    Type
 ----------------------- -------- -------
 ACCOUNT_NUMBER          NOT NULL NUMBER(13)
 FROM_DATE               NOT NULL DATE
 TO_DATE                 NOT NULL DATE
 COD_TO_BOD_FACTOR       NOT NULL NUMBER(6,2)
 CONSUMPTION             NOT NULL NUMBER(15)
 SAMPLE_DATE             NOT NULL DATE
 COD_CONTENT             NOT NULL NUMBER(10)
 SS_CONTENT              NOT NULL NUMBER(10)
 DATE_SUBMITTED                   DATE    --  this is the new column

The SQL Server source table definition is:

[Account_Number] [varchar](30) NULL,
[From_Date] [datetime] NULL,
[To_Date] [datetime] NULL,
[Factor] [numeric](15, 2) NULL,
[Consumption] [numeric](15, 2) NULL,
[Sample_date] [datetime] NULL,
[COD] [numeric](15, 2) NULL,
[SS] [numeric](15, 2) NULL
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,948 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,651 questions
{count} votes

5 answers

Sort by: Most helpful
  1. Olaf Helper 44,936 Reputation points
    2020-09-17T13:18:20.087+00:00

    supplied inconsistent metadata

    A common issue with Linked Server to Oracle, there is a support article for available:

    https://support.oracle.com/knowledge/Oracle%20Database%20Products/2484393_1.html


  2. Tom Phillips 17,736 Reputation points
    2020-09-17T18:42:35.143+00:00

    Try naming your new column in the select
    ...
    Select [Account_Number]
    ,[From_Date] ,[To_Date]
    ,[Factor] ,[Consumption]
    ,[Sample_date] ,[COD]
    ,[SS]
    , ( select min([Sample_date])
    from dbo.ORA_Interface
    where Account_Number = p.Account_Number ) as DateSelected
    ...


  3. m 4,271 Reputation points
    2020-09-21T06:47:17.96+00:00

    Hi @Charles M ,

    Test that it works by running a 4 part query.

    <Linked server name> <Database name> (if no specific database name then just use “..”) <Schema> <Table Name>

    For your case,please change your query as next:
    select ... from <Linked Server name>.<database name>.cbd.WS_CBD_PACS;
    select ... from <Linked Server name>.<database name>.dbo..ORA_Interface

    Or you can create another linked server to test and also testing with 4 part query: creating-a-sql-server-2014-linked-server-for-an-oracle-11g-database

    BR,
    Mia


    If the answer is helpful, please click "Accept Answer" and upvote it.


  4. Charles M 1 Reputation point
    2020-09-28T16:31:08.93+00:00

    Okay, I don't know why I didn't think of this sooner, but for some reason, adding

    OPTION (RECOMPILE)
    

    to the query makes this work. Again, I don't know why the metadata is perfectly fine the 1st execution and wrong on subsequent tries. But by adding recompile, every run is the "first" execution.

    0 comments No comments

  5. Charles M 1 Reputation point
    2022-03-29T12:58:52.707+00:00

    It turns out this was a bug in the Oracle driver.

    Oracle patches this in Patch 31892754: 19C OLEDB PROVIDER EXECUTION FAILS WITH INCONSISTENT METADATA IN A LINKED SERVER (Requires Oracle login)
    This patch requires Oracle 19.3 and CU 31247621 (but no more recent CUs than that).
    This patch was introduced into the 19.3 drivers with CU 19.11, released Apr 27, 2021.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.