Oracle Linked Server query error - unable to read certain data

Question

Oracle Linked Server query error - unable to read certain data

denappel

SSC-Addicted

Points: 411
More actions
November 16, 2011 at 3:25 am

#260048

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

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

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 SSC-Addicted Points: 411 More actions · Answer 2

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 SSCertifiable Points: 6355 More actions · Answer 3

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!

Devendra Shirbad SSC-Forever Points: 42493 More actions · Answer 4

Please verify DBCOLUMNFLAGS properties for OLEDB provider.

It should be 'DBCOLUMNFLAGS_ISFIXEDLENGTH = false' for VARCHAR.

denappel SSC-Addicted Points: 411 More actions · Answer 5

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 SSC Enthusiast Points: 128 More actions · Answer 6

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 SSCertifiable Points: 6355 More actions · Answer 7

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 SSC Enthusiast Points: 128 More actions · Answer 8

Thanks for your reply. I will check out the metadata.

Sincerely! Jeannine

raviteja.jampani36 SSC Rookie Points: 31 More actions · Answer 9

Dev - Monday, November 28, 2011 11:52 PM
Please verify DBCOLUMNFLAGS properties for OLEDB provider.It should be 'DBCOLUMNFLAGS_ISFIXEDLENGTH = false' for VARCHAR.

Please let me know where to check and change this property for OLEDB provider