x

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?

more ▼

asked Dec 17 '09 at 08:15 AM in Default

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.2k 8 11 21

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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?

more ▼

answered Dec 18 '09 at 07:34 AM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.2k 8 11 21

Anyone has a better solution for the problem than the one I described?
Dec 22 '09 at 08:18 AM Pavel Pawlowski
(comments are locked)
10|1200 characters needed characters left
more ▼

answered Dec 18 '09 at 05:42 AM

sp_lock gravatar image

sp_lock
9k 24 27 30

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1933
x101
x37
x22

asked: Dec 17 '09 at 08:15 AM

Seen: 3609 times

Last Updated: Dec 17 '09 at 08:46 AM