Forum Stats

  • 3,768,548 Users
  • 2,252,809 Discussions
  • 7,874,617 Comments

Discussions

Categories

SQL Server Linked Server Throws Metadata Error

CharlesM_Dayton
CharlesM_Dayton Member Posts: 2 Red Ribbon
in Oracle Provider for OLE DB

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.

User_76CI7User_0NVAZ
· Share on TwitterShare on Facebook

Best Answer

Answers

  • User_76CI7
    User_76CI7 Member Posts: 2 Green Ribbon

    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

    · Share on TwitterShare on Facebook
  • CharlesM_Dayton
    CharlesM_Dayton Member Posts: 2 Red Ribbon

    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!

    · Share on TwitterShare on Facebook
  • User_76CI7
    User_76CI7 Member Posts: 2 Green Ribbon

    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.

    · Share on TwitterShare on Facebook
  • User_0NVAZ
    User_0NVAZ Member Posts: 2 Green Ribbon

    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?

    · Share on TwitterShare on Facebook