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
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
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
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
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
...
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.
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.
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.