1

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 
  • 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 at 15:58
  • @Graham are you suggesting on the temp table on the Oracle side or the SQL side? – CaM Sep 18 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 at 15:06
  • I was suggesting a temp table on the SQL Server side as an intermediary. – Graham Sep 19 at 17:21
  • I changed to copy data to a #temp table, then copy from there to the Oracle table. Again, it runs fine the 1st time after issuing the Alter Proc statement, then fails on subsequent executions with the same error message. – CaM Sep 21 at 15:06

Your Answer

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

Browse other questions tagged or ask your own question.