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


Oracle Linked Server query error - unable to read certain data


Oracle Linked Server query error - unable to read certain data

Author
Message
denappel
denappel
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 14
We have a problem with a query from a SQL Server 2008R2 - 64bit to an Oracle Linked Server.

We use the Oracle 64-bit driver for Windows.

When we query a certain table we get the following error:

Msg 7347, Level 16, State 1, Line 1 OLE DB provider 'OraOLEDB.Oracle' for linked server 'SERV_VISION2' returned data that does not match expected data length for column '[SERV_VISION2]..[VISION].[INV_M001].CUS_NM'. The (maximum) expected data length is 30, while the returned data length is 24.

The problem seems to be the data in the CUS_NM column. In the rows where he gives this error the data is like this:

¿¿¿

So like a question mark but upside down. There doesn't seem to be a problem with the length of the column, for one reason he can't handle this data I guess.
Could it be a collation problem?

Did anyone experience this problem? Anyone a clue how to solve this?

Thanks in advance for your answers!

Best regards,

Koen
Lowell
Lowell
SSC Guru
SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)

Group: General Forum Members
Points: 72650 Visits: 40947
i see there is a connect item for two other ODBC drivers with the same issue here....
http://connect.microsoft.com/SQLServer/feedback/details/126972/mssql2005-returned-data-that-does-not-match-expected-data-length-for-column

one poster say if he checks this in the settings he gets the error...which implies if it's not set, it will work?

In ODBC options I checked "Report Char columns as Wide Char columns" and I get the following error:



Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
denappel
denappel
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 14
Hi Lowell,

Thanks for your answer.
I couldn't resolve the issue with setting
the force SQL_WCHAR support (in odbc datasources)
for the oracle connection to true or to false.

Neither setting made any difference...

Regards,

Koen
davoscollective
davoscollective
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1675 Visits: 1008
Hi,
I had this problem recently using OpenQuery to a linked Oracle 10g server from SQL Server 2005.

I had this error
Msg 7347, Level 16, State 1
OLE DB provider 'OraOLEDB.Oracle' for linked server '####' returned data that does not match expected data length for column '[OraOLEDB.Oracle].'####''. The (maximum) expected data length is 64, while the returned data length is 16.

I used this conversion function inside the openquery statment (on the Oracle side of things):
To_NChar()
Put that around the columns reporting length limitations.
-- Note that To_Char does not work which is what I tried first.

Distributed queries attempt to match data types.
(BOL)
http://msdn.microsoft.com/en-us/library/ms175970.aspx

Too many types, not generally helpful.

I found this page of mappings more useful
http://www.carlprothman.net/Default.aspx?tabid=97

Most of the time when I am using OpenQuery it is against an OLAP cube and the data retuned is Ntext (I can't find anything in BOL about that so I can't verify that conjecture). When I need to insert those into a SQL Server Varchar field I have to explicitly cast it as Varchar on the SQL Server side of things.

That didn't work in this situation with Oracle.

I took a stab in the dark that if I could get it into the mapped version of Ntext then I could do what I want at the SQL Server end. In that last link apparently NCLOB is the equivalent of Ntext so this Oracle function To_NCLOB() works, as does To_NChar() (returns the Oracle datatype Nvarchar2). I think NChar is preferable to the NCLOB conversion for some superstituous reason (smaller datatypes are cooler?).


Whacky error anyhow. I put in some hardcode strings in place of the columns and tried various lengths and found it is actually expecting exactly 64 bytes (32 nchars) despite the error saying its a maximum length. Oracle confuses me... I love SQL Server!
Dev
Dev
SSCrazy Eights
SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)

Group: General Forum Members
Points: 9058 Visits: 1602
Please verify DBCOLUMNFLAGS properties for OLEDB provider.

It should be 'DBCOLUMNFLAGS_ISFIXEDLENGTH = false' for VARCHAR.
denappel
denappel
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 14
Hi,

Thanks a lot for your help, this solution works for me! :-)
Damn we were already looking for more exotic solutions,
I'm quite happy that we can solve it this way.

Indeed if I run the query distributed with openquery,
and I put To_NChar() around the column that gave the error
it works smoothly. (below the working query)

SELECT *
FROM OPENQUERY(SERV_VISION, 'SELECT LANG_DIV
,WARE_DIV
,CAUSE_CD
,To_NChar(CAUSE_NM) as CAUSE_NM
,UNKNOWN_FLG
,DR_DIV
,CRT_DT
,CRT_ID
,UPD_DT
,UPD_ID
,CLASS_CD1
,CLASS_CD2 from VISION.INV_M008')

Thanks a lot!

Best regards,

Koen
j9fortier
j9fortier
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 21
Hi,

I having same issue with trying to select data from a "Universe" database via a SQL Server Link.
The SQL Server is 64 Bit. The Universe driver is 64 bit. I am able to create the linked server and return some, but not all records.
I receive the error message

Msg 7347, Level 16, State 1, Line 2
OLE DB provider 'MSDASQL' for linked server 'ACCTSERV' returned data that does not match expected data length for column '[ACCTSERV]...[JC_PROJ_INFO].JC_PROJ_KEY'. The (maximum) expected data length is 8, while the returned data length is 9.

I have tried using CAST , LTRIM, RTRIM, SUBSTRING. I am at my wit's end trying to find a solution on the web.

Any other suggestions would be greatly appreciated.

Jeannine
davoscollective
davoscollective
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1675 Visits: 1008
You should probably have started that in a new thread, your question will get lost if you tag it on to the end of an old thread like this.

Anyway, assuming you are using the 'rocket u2' universe product

With this tool:
U2 Metadata Manager (U2 MDM)

You can manage the metadata in your universe files.

I am guessing that SQL Server is reading the metadata of your universe data structures, which doesn't match the contents of the universe data structures. Get the metadata up to date and it should be fine.
j9fortier
j9fortier
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 21
Thanks for your reply. I will check out the metadata.

Sincerely! Jeannine
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