SQL Server linking to Oracle

[mrdenny]

OK, here is a strange one. We are setting up a linked server from SQL Server 2000 (also tried SQL 2005) to our Oracle data warehouse.

Now some of the tables work fine. Other's are failing with this error:

OLE DB error trace [Non-interface error: Column 'CAL_JULIAN_DAY_NUM' (compile-time ordinal 14) of object '"ORIG_EDW"."GLOBAL_CALENDAR_DIM"' was reported to have a DBTYPE of 130 at compile time and 5 at run time].
[red]Msg 7356, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' supplied inconsistent metadata for a column. Metadata information was changed at execution time.[/red]

Now one of the tables that isn't working is ORIG_STATUS_DIM. Here is the create code for the object on the Oracle side.

CREATE TABLE ORIG_STATUS_DIM
(
  STATUS_SK                     INTEGER         NOT NULL,
  SOURCE_SYSTEM_CD              VARCHAR2(50 BYTE),
  STATUS_CUR_CD                 VARCHAR2(50 BYTE),
  STATUS_CUR_STATUS_DESC        VARCHAR2(100 BYTE),
  STATUS_BKD_CD                 VARCHAR2(50 BYTE),
  STATUS_BKD_DESC               VARCHAR2(100 BYTE),
  STATUS_USER_ID_DAILY_NAME     VARCHAR2(100 BYTE),
  STATUS_USER_ID_INTRADAY_NAME  VARCHAR2(100 BYTE),
  STATUS_APP_CD                 VARCHAR2(50 BYTE),
  STATUS_DECISION_CD            VARCHAR2(50 BYTE),
  STATUS_DECISION_TYPE_CD       VARCHAR2(50 BYTE),
  STATUS_AUTO_CD                VARCHAR2(50 BYTE),
  STATUS_CONTRACT_CD            VARCHAR2(50 BYTE),
  STATUS_FUND_CD                VARCHAR2(50 BYTE),
  STATUS_STAGE_DESC             VARCHAR2(100 BYTE),
  EFF_DT                        DATE,
  END_DT                        DATE,
  CUR_IND                       VARCHAR2(50 BYTE),
  CREATE_DT                     DATE,
  MOD_DT                        DATE,
  STATUS_AUTODECISION_CD        VARCHAR2(50 BYTE)
)
TABLESPACE ORIG_DIM_TS
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          80K
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

One of the tables that is working is GLOBAL_TIME_DIM. It's create statement looks like this.

CREATE TABLE GLOBAL_TIME_DIM
(
  TIME_SK                     INTEGER           NOT NULL,
  TIME_SECS_ELAPSED_NUM       INTEGER,
  TIME_24_DESC                VARCHAR2(100 BYTE),
  TIME_12_DESC                VARCHAR2(100 BYTE),
  TIME_HR_24_NBR              VARCHAR2(50 BYTE),
  TIME_HR_12_NBR              VARCHAR2(50 BYTE),
  TIME_MINS_NBR               VARCHAR2(50 BYTE),
  TIME_SECS_NBR               VARCHAR2(50 BYTE),
  TIME_THIRTY_MIN_BAND_DESC   VARCHAR2(100 BYTE),
  TIME_FIFTEEN_MIN_BAND_DESC  VARCHAR2(100 BYTE),
  TIME_TEN_MIN_BAND_DESC      VARCHAR2(100 BYTE),
  TIME_FIVE_MIN_BAND_DESC     VARCHAR2(100 BYTE),
  TIME_AM_PM_IND              VARCHAR2(50 BYTE),
  EFF_DT                      DATE,
  END_DT                      DATE,
  CUR_IND                     VARCHAR2(50 BYTE),
  CREATE_DT                   DATE,
  MOD_DT                      DATE
)
TABLESPACE ORIG_DIM_TS
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          80K
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

Can anyone see why the ORIG_STATUS_DIM table isn't working but the GLOBAL_TIME_DIM table is working? It makes no sense to us what so ever. If you want to see the indexes and Constraints let me know and I can post them. There are quite a few.

We have tried using both the Oracle and Microsoft driver to connect to the database and we have the same problem with both drivers. when we are on the console of the server we can use sqlplus and the Oracle software to pull data from the tables that don't work via the linked server and they work fine.

This tells me that the problem is that SQL Server doesn't like the something about the table that Oracle is passing back. We are open to just about anything no matter how crazy. We can't do anything much to the data warehouse unless we can show a major problem with the data warehose design.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[yelworcm]

Can you select other columns than the one in the error message above (other than CAL_JULIAN_DAY_NUM)? Just want to see if this is a table problem, or some data conversion error that affects only some columns. Can you give an error from the affected table by chance?

 

[mrdenny]

No it doesn't matter what columns I put in the select list.

The only error given is posted above.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[maswien]

I can only guess this is the story:

it looks like ORIG_EDW is a view defined in SQL Server for a while, then the defination of the table in oracle is changed. That could be the reason you see the datatype inconsistence error between the compile time and run time.

 

[SQLDenis]

Could you show the create script for the table GLOBAL_CALENDAR_DIM and specifically column 'CAL_JULIAN_DAY_NUM'

someone with the same problem found an answer here (

http://www.issociate.de/board/post/187431/problem_making_Oracle_linked_server_work.html)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[mrdenny]

ORIG_EDW is not a view on the SQL Server. ORIG_EDW is the schema on the Oracle server. The objects GLOBAL_TIME_DIM and ORIG_STATUS_DIM are both objects within the ORIG_EDW schema.

I post the wrong error. We know why the GLOBAL_CALENDAR_DIM isn't working.

When I run the following code from SQL 2000

select *
from EDW_PROD..ORIG_EDW.ORIG_STATUS_DIM

I get this error.

OLE DB error trace [Non-interface error: OLE/DB provider returned an invalid schema definition.].
[red]Msg 7317, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' returned an invalid schema definition.[/red]

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[maswien]

I tried on my machine, I could only replicate the the first error you posted which is "OLE DB provider 'MSDAORA' supplied inconsistent metadata for a column. Metadata information was changed at execution time."

I wasn't able to replicate the second error you posted, it seems a lots of people have the same problem from the google and I can't find any of them helpful. Following is my suggestion:

1. try using the ORACLE OLEDB instead microsoft

2. if 1 not OK, update the machine with the latest mdac

3. if 2 not OK, try using OPENQUERY() instead of four
part name. As far as I know, almost all the distributed
query with four part name can be converted to OPENQUERY
syntax.

 

[mrdenny]

1. I've tried both the Oracle drivers and the SQL Server drivers.

2. I've got the latest MDAC 2.8sp1.

3. Developers aren't allowed to use OPENQUERY. The Oracle box has several hundred million records per table. We are also keeping the rules between SQL 2000 and SQL 2005 the same, and we aren't enabling OPENQUERY on SQL 2005.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com