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

Oracle Linked Server query error - unable to read certain data Expand / Collapse
Author
Message
Posted Wednesday, November 16, 2011 3:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 24, 2012 2:08 AM
Points: 3, 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
Post #1206510
Posted Wednesday, November 16, 2011 5:32 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:07 AM
Points: 12,903, Visits: 32,145
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1206588
Posted Wednesday, November 16, 2011 6:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 24, 2012 2:08 AM
Points: 3, 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
Post #1206606
Posted Monday, November 28, 2011 6:40 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Saturday, September 27, 2014 5:20 AM
Points: 467, Visits: 868
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!
Post #1212845
Posted Monday, November 28, 2011 11:52 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 6:53 PM
Points: 1,932, Visits: 1,589
Please verify DBCOLUMNFLAGS properties for OLEDB provider.

It should be 'DBCOLUMNFLAGS_ISFIXEDLENGTH = false' for VARCHAR.


Dev

Devendra Shirbad | BIG Data Architect / DBA | Ex-Microsoft CSS (SQL 3T)
*** Open Network for Database Professionals ***

LinkedIn: http://www.linkedin.com/in/devendrashirbad
Post #1212941
Posted Tuesday, November 29, 2011 3:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 24, 2012 2:08 AM
Points: 3, 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
Post #1213041
Posted Thursday, August 7, 2014 5:29 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 5:03 PM
Points: 2, Visits: 19
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
Post #1600960
Posted Thursday, August 7, 2014 8:11 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Saturday, September 27, 2014 5:20 AM
Points: 467, Visits: 868
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.

Post #1600998
Posted Friday, August 8, 2014 9:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 5:03 PM
Points: 2, Visits: 19
Thanks for your reply. I will check out the metadata.

Sincerely! Jeannine
Post #1601223
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse