Discussions
Categories
- 196.7K All Categories
- 2.2K Data
- 234 Big Data Appliance
- 1.9K Data Science
- 449.8K Databases
- 221.5K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 549 MySQL Community Space
- 477 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 532 SQLcl
- 4K SQL Developer Data Modeler
- 186.8K SQL & PL/SQL
- 21.2K SQL Developer
- 295.3K Development
- 17 Developer Projects
- 138 Programming Languages
- 292K Development Tools
- 104 DevOps
- 3.1K QA/Testing
- 645.9K Java
- 27 Java Learning Subscription
- 37K Database Connectivity
- 153 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 17 Java Essentials
- 157 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 203 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 389 LiveLabs
- 37 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.6K Other Languages
- 2.3K Chinese
- 170 Deutsche Oracle Community
- 1K Español
- 1.9K Japanese
- 230 Portuguese
SQL Server Linked Server Throws Metadata Error

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 or execute it a second time, it fails. 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, though I've also tried standard insert syntax:
insert into [ORADEV-LINK]..COD.WS_PACS
...
just to see if that fixes it, but it gives the same error. We have also tried dropping and rebuilding the Oracle table, to see if there's something weird about the Add Column that is messing up OLE, but that didn't fix it either.
The function is:
ALTER Procedure [COD].[Interface_Upload]asSET 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 COD.WS_PACS') Select [Account_Number] ,[From_Date] ,[To_Date] ,[Factor] ,[Consumption] ,[Sample_date] ,[COD] ,[SS] , ( select min([Sample_date]) from cod.WRS_Interface where Account_Number = p.Account_Number ) as Date_Submitted from cod.WRS_Interface p ;
@rowcount;
The Oracle table definition that I'm trying to insert into is:
Null? Type----------------------- -------- -------ACCOUNT_NUMBER NOT NULL NUMBER(13)FROM_DATE NOT NULL DATETO_DATE NOT NULL DATECOD_TO_BOD_FACTOR NOT NULL NUMBER(6,2)CONSUMPTION NOT NULL NUMBER(15)SAMPLE_DATE NOT NULL DATECOD_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
Any ideas why this is failing and how to get past it? Everything I've been able to find says the issue is either around Oracle's varchar2(5) vs varchar2(5 char) style syntaxes (no varchar columns used) or to switch to OpenQuery, which I'm using. So I'm at a loss as how to get this to work.
Best Answer
-
It seems that recently they fixed this issue at oracle support.
The cause of this problem has been identified as:
Bug 31892754 - 19C OLEDB PROVIDER EXECUTION FAILS WITH INCONSISTENT METADATA IN A LINKED SERVER
Solution
Issue has been fixed in Patch 31892754 on 19c for Windows
Answers
-
Hi Charles,
Did you ever get a solution to this? I am getting the same thing with the same Oracle client. I tried creating a view on the table, but get the same result. Will probably try and write an Oracle stored procedure and pass in parameters from SQL Server but don't really want to do this.
Cheers
Owain
-
Yes, I did finally find a way past the error.
insert into [ORADEV-LINK]..COD.WS_PACS (ACCOUNT_NUMBER, FROM_DATE, TO_DATE, COD_TO_BOD_FACTOR, CONSUMPTION, SAMPLE_DATE, COD_CONTENT, SS_CONTENT, DATE_SUBMITTED) select [Account_Number] ,[From_Date] ,[To_Date] ,[Factor] ,[Consumption] ,[Sample_date] ,[COD] ,[SS] , @minDate from cod.WRS_Interface p option (recompile);
I don't know exactly why SQL / Oracle OLE aren't communicating the metadata properly, but adding the OPTION RECOMPILE flag into the insert statement is what solved the issue for us. Basically, every run of the insert statement is effectively the "first" run, which always worked in our case. It didn't start to fail until 2nd and subsequent runs after the original compile phase.
Hope this helps you!
-
Thanks for getting back to me, it didn't help me resolve my issue but the way you have written it up helps me explain to other.
-
We run into the same issue.
The workaround OPTION RECOMPILE is working, but it is not performing. Query that is normally taking 1 minute is taking now 15 minutes. This not the solution we are looking for.
Is there already a solution for this issue?
-
It seems that recently they fixed this issue at oracle support.
The cause of this problem has been identified as:
Bug 31892754 - 19C OLEDB PROVIDER EXECUTION FAILS WITH INCONSISTENT METADATA IN A LINKED SERVER
Solution
Issue has been fixed in Patch 31892754 on 19c for Windows