2

I have a function in SQL Server 2019 that copies data from a local table to an Oracle 11.2.0.4 table via Linked Server, using Oracle 19.3 client. Recently, a new column was added to the Oracle table, so I've updated the insert function to include 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 then starts throwing the error from then on. ALL 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.

Everything I can find says that if you're getting metadata errors, go 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 destination table definition 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

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 
9
  • What happens if you pass you data through a temp table first? E.g. Insert Into a temp table then use that temp table to do the cross server insert. I suggest explicitly declaring your temp table if an implicit one doesn't work. – Graham Sep 17 '20 at 15:58
  • @Graham are you suggesting on the temp table on the Oracle side or the SQL side? – CaM Sep 18 '20 at 15:05
  • I tried switching back to a standard insert into .. select query and that failed. I've tried rebooting the SQL server; that failed too. I've even tried dropping the Oracle table and recreating it. – CaM Sep 18 '20 at 15:06
  • 1
    For giggles, try adding an OPTION (RECOMPILE) on the INSERT. – Graham Sep 24 '20 at 19:37
  • 1
    I'll add that as an answer so in the future someone with the same issue doesn't need to wade through all the missteps to find out if things got sorted. – Graham Sep 29 '20 at 19:33
2

Use OPTION (RECOMPILE) on the INSERT

4
  • 1
    Thank you! This one drove me nuts. May I ask why this works? – Metaphor Nov 23 '20 at 22:17
  • 1
    For an unknown reason reusing the a query plan is causing a failure. Instead of addressing the root cause (because I have no idea the root cause), I provided a workaround; the recompile avoids reusing a plan. – Graham Dec 4 '20 at 23:57
  • I wonder if this isn't some kind of bug on MS's part? – CaM Dec 7 '20 at 18:39
  • Turns out it's a bug on Oracle's part. – CaM Jan 21 at 14:58
0

It turns out this problem is an Oracle Bug. Patch 31892754: 19C OLEDB PROVIDER EXECUTION FAILS WITH INCONSISTENT METADATA IN A LINKED SERVER addresses this problem.

It requires Oracle 19.3 and CU 31247621 (but no more recent CUs than that).

Oracle says this patch will be included in Oracle 19.11, so I'm assuming that's a CU due in 2021.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.