March 15, 2016 at 1:04 pm
Hi all, long time viewer, first time poster.
My 2012 SQL Server has a linked connection with our legacy AS400. The connecion has been working flawlessly for some time now, however, I am trying to connect to some data and I have run into an issue.
With a new file in the AS400, I run my select statement and some of the fields come back looking as if they are hex format ("0x40404040404040F1F1F2F6F2" or "ðððñò"). In speaking with our AS400 developers, the fields in question are formatted as ALPHA then have a length of less than 30. The ALPHA fields that are 30 or greater return data just fine.
Has anyone experienced an issue similar?
March 16, 2016 at 9:32 am
Can you do a CAST() of the ALPHA to CHAR\VARCHAR in the query? I honestly had never heard of the ALPHA type until reading this post. Doesn't even seem like it is a DB2 type if that's what you are connecting to on the AS400.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 16, 2016 at 10:57 am
Well...
When I CAST it as nvarchar, I get this: ??????
When I CAST it as varchar, I get: @@@@@@@ññòöñ
Crazy.
March 21, 2016 at 8:02 am
Have the AS/400 developers look at the field definitions with DSPFFD command. It sounds like they may be defined with a CCSID of 65535 which means it is hex and doesn't have a defined CCSID. Look at the fields that work and they probably have something else (37 would be US English). The AS/400 at heart is an EBCDIC machine and the fields must be defined with a valid CCSID for the conversion to work properly. If they have 65535 then no conversion is done and since it is EBCDIC it won't be usable on the ASCII side.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply