Skip to Main Content

DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

SQL Server Linked Server Throws Metadata Error

CharlesM_DaytonSep 17 2020

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]

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 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 ;
return @@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 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

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.

This post has been answered by User_0NVAZ on Jan 21 2021
Jump to Answer
Comments
Post Details
Added on Sep 17 2020
6 comments
5,479 views