SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server Link to Oracle Inconsistent metadata problem


SQL Server Link to Oracle Inconsistent metadata problem

Author
Message
kats99
kats99
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 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
kats99
kats99
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 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
arunyadav007
arunyadav007
Valued Member
Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)

Group: General Forum Members
Points: 68 Visits: 299
Have you checked this link?

http://support.microsoft.com/kb/251238
kats99
kats99
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 75
Ys I did thankyou but it does not apply
michaelkinlb
michaelkinlb
Say Hey Kid
Say Hey Kid (667 reputation)Say Hey Kid (667 reputation)Say Hey Kid (667 reputation)Say Hey Kid (667 reputation)Say Hey Kid (667 reputation)Say Hey Kid (667 reputation)Say Hey Kid (667 reputation)Say Hey Kid (667 reputation)

Group: General Forum Members
Points: 667 Visits: 1003
Did you ever resolve this issue with the linked server to Oracle?
kats99
kats99
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 75
I am afraid not. I had to use the open query workaround. Sad
emanuele.lanza
emanuele.lanza
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 6
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
michaelkinlb
michaelkinlb
Say Hey Kid
Say Hey Kid (667 reputation)Say Hey Kid (667 reputation)Say Hey Kid (667 reputation)Say Hey Kid (667 reputation)Say Hey Kid (667 reputation)Say Hey Kid (667 reputation)Say Hey Kid (667 reputation)Say Hey Kid (667 reputation)

Group: General Forum Members
Points: 667 Visits: 1003
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.
emanuele.lanza
emanuele.lanza
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 6
I don't know where is located the problem (table/view and column) because the message of the agent don't explain that.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search