Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Server Link to Oracle Inconsistent metadata problem Expand / Collapse
Author
Message
Posted Monday, December 3, 2012 8:35 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, August 30, 2013 7:22 AM
Points: 10, Visits: 75
Hi
I have created a linked server to Oracle using the Oracle OLE DB provider "ORAOLEDB.ORACLE" from SQL 2008. I can query the Oracle database using OPENQUERY but when I try a simple SELECT COUNT(*) from LK_ORA..USR.TAB1 I get an error:

Msg 7356, Level 16, State 1, Line 1
The OLE DB provider "ORAOLEDB.ORACLE" for linked server "LK_ORA" supplied inconsistent metadata for a column. The column "COL_CODE" (compile-time ordinal 1) of object ""USR"."TAB1"" was reported to have a "LENGTH" of 12 at compile time and 24 at run time.

I've found an article that says when SQL Server retrieves metadata from Oracle in order to compile the query, Oracle reports the data type to be varchar(255), but when it actually returns the data, the data type is in fact nvarchar(255). (But it may not be called nvarchar in Oracle.)

Anyone know how to get around this? I've tried various options on my linked server connection in SQL with no luck so far

Many Thanks
Post #1391984
Posted Tuesday, December 4, 2012 4:07 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, August 30, 2013 7:22 AM
Points: 10, Visits: 75
I am looking at the collations on the linked server to see if that helps

Here are my configurations:
Oracle 11g
NLS_LANGUAGE = AMERICAN
NLS_TERRITORY = AMERICA
NLS_CHARACTERSET = WE8ISO8859P15
NLS_NCHAR_CHARACTERSET = AL16UTF16


SQL 2008 R2 RTM x64
Linked Server Provider - OraOLEDB.Oracle
Linked Server settings - Collation Compatible = False, Use Remote Collation = True, Collation Name = SQL_Latin1_General_CP1_CI_AS

I have also tried with Collation Compatible = True and no Collation Name

Any help would be great
Thanks
Post #1392347
Posted Tuesday, December 4, 2012 4:15 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 24, 2014 11:46 PM
Points: 42, Visits: 290
Have you checked this link?

http://support.microsoft.com/kb/251238
Post #1392352
Posted Tuesday, December 4, 2012 4:18 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, August 30, 2013 7:22 AM
Points: 10, Visits: 75
Ys I did thankyou but it does not apply
Post #1392355
Posted Tuesday, May 21, 2013 2:05 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 4:13 PM
Points: 574, Visits: 769
Did you ever resolve this issue with the linked server to Oracle?
Post #1455212
Posted Tuesday, May 21, 2013 2:23 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, August 30, 2013 7:22 AM
Points: 10, Visits: 75
I am afraid not. I had to use the open query workaround.
Post #1455217
Posted Thursday, September 5, 2013 6:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 3, 2013 7:14 AM
Points: 2, Visits: 5
Hi,
i've a similar proble. I've some procedures that uses a oracle linked server, but:
- if i run manually procedures no errors occur
- if i run them in a scheduled process it breaks for inconsistent metadata
- i've no idea which tables/views are the problem source, how can i investigate on it?
- i've tried to use Profiler without success

thanks
Post #1491742
Posted Thursday, September 5, 2013 1:41 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 4:13 PM
Points: 574, Visits: 769
I ended up creating a view of the Oracle table using the TO_CHAR function on the problem columns that were defined as VARCHAR2. I then just use a linked server to Oracle. You could also use the TO_CHAR function directly in your query if you did not want to create the view.

SELECT * FROM OPENQUERY(LinkedServer,'SELECT to_char(gbaid)
FROM PRODDTA.F0902
WHERE gbfy = 99
')
Regards.
Post #1491937
Posted Friday, September 6, 2013 5:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 3, 2013 7:14 AM
Points: 2, Visits: 5
I don't know where is located the problem (table/view and column) because the message of the agent don't explain that.
Post #1492164
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse