AS400 EBCDIC 870 code page to Win1250 on SQL Server 2005

Question

question

Pavel Pawlowski asked Dec 17, '09

AS400 EBCDIC 870 code page to Win1250 on SQL Server 2005

I have a linked server to DB2 database on AS400 machine. The data in the db are encoded using EBCDIC 870 code page and contain characters like Ž (hex B8 in EBCDIC), Ř (hex AE).

The SQL server uses Win1250 code page and I need to receive a correct characters, when querying data from the linked server by OPENQUERY like for example

SELECT * FROM OPENQUERY(AS400, 'SELECT * FROM SSCUSTP')

The problem is that SQL Server has no native support for the EBCDIC 870 code page.

If you use

SELECT *
FROM fn_helpcollations()

You can see a few EBCDIC collation, but not 870.

When simply calling the above mentioned select to retrieve data, I receive wrong characters for non ASCII characters. What more, in the result more EBCDIC characters are converted tot he same Win1250 characters.

We are using IBM DB2 ODBC driver for the Linked Server.

Any help for this?

sql-server-2005 linked-server odbc collation

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2009-12-18T07:34:30Z

Pavel Pawlowski answered Dec 18, '09

The discussion on SSC mentioned do not directly resolve my problem..

The discussion is related to SSIS, but could be possibly applicable out of SSI.

They retrieve raw data and locate fields in them.

I need to use Live data using the SELECT statements in the OPENQUERY.

AS I wrote in the question, I was googling and binging about this a lot and didn't found any suitable solutin.

Finaly.. I found solution by myself using HEX function of DB2 and CRL scalar function (or T-SQL scalar function with Convertion table) on the SQL Server side which allows convertion of fields I want :-)

I have a description of this solution on my site here

The CRL function mentioned on my site allows encoding from EBCDIC 870 to any encoding you want. Generally after slight modification it can convert any source encoding to any destination encoding

Maybe some other people will find this solution as suitable for such conversions. It's simple and quick to implement.

Or better, does anybody know about a better solution?

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2009-12-18T05:42:39Z

sp_lock answered Dec 18, '09

Maybe the SSc post will help?

http://www.sqlservercentral.com/Forums/Topic430809-148-1.aspx

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question