반응형
/********************************************************************************************
-- Title : [2k8] Linked Server to Oracle에서 7356 에러 메시지
-- Reference : unlocalize.com
-- Key word : 연결된 서버 오라클
********************************************************************************************/

-- SQL linked Server to Oracle에서 4 Part Name 쿼리시 오류 메시지가 출력.
select * from linked_server..test.test;

-- 오류 메시지
메시지 7356, 수준 16, 상태 1, 줄 2
연결된 서버 "LINKED_DSGNORCL"의 OLE DB 공급자 "MSDAORA"이(가) 열에 대해 일관성이 없는
메타데이터를 제공했습니다.
개체 ""DEVDSGN"."RSRC_SECT""의 열 "RSRC_SECT_NM"(컴파일 시 서수 2)이(가) 컴파일 중에는 "DBTYPE"/130이(가) 있고
런타임 중에는 129이(가) 있는 것으로 보고되었습니다.

-- 구글링에서 찾은 가장 이상적인 답변?
Since you are using sql2k5, not 2k8, changing the version to 100 would seem unlikely to fix your problem.

I had a similar problem with links to Oracle 9 & 10 using the Oracle OLEDB provider from SQLServer 2005 SP2.
I don't recall if I had the exact error msgs you report but it sounds similar.

A call to MS Premium support eventually disclosed that the problem was with that provider. We found that numeric values that ended in '0' would confuse the provider. It was reading that value as the number of decimal places in the column (0:= integer). So, the read would proceed properly until the first value ending in zero. Then it would fail.

I ended up using openquery() to read all data with inline conversions all numerics to character types before inserting the rows to a local SQL Server table. Then, I altered the local columns to appropriate types. Yes, this was a major pain but worked.

If you would like more information on this, let me know.

Some have reported better luck with the MS provider for Oracle 8 used against later versions. This seems to be workable where no later vintage data types are involved (requiring a later version of the Oracle provider). Seemingly, Oracle stopped providing MS with information necessary to update their provider so they froze on version 8. This may be an urban myth.

SSIS does not seem to have this problem - Connectors are not Providers. The recently released connector for Oracle and Teradata by Attunity (check MS download) seems to perform very well but does require Sql 2k8.



반응형

+ Recent posts