notes on character encoding conversion between SQL Server and AS400

Character encoding is a difficult topic because different companies have developed different terminologies and different standard.  For the Microsoft products, the default character encoding is Windows-1252.  It is being used in the SQL Servers.  For IBM servers, it is EBCDIC encoding schema.  In some AS400 system, both CCSID 37 and CCSID 65535 are being used.  The CCSID 37 seems to be mostly used.

The IBM iSeries OLE DB provider translates the character encoding when data are being sent from SQL Server to the AS400.  In most cases, the translation of Windows 1252 to CCSID 37 works.  But, there are 26 Windows 1252 characters cannot be translated, see the table below.  The CCSID 37 does not have the matching characters.  So, the program (like SSIS package) has to ignore the error and let the iSeries driver change the unsupported characters to question mark “?”.  Or, the program has to manually convert them, for example changing the character Ž to the character Z.

 

Windows 1252

character

code

128

130

ƒ

131

132

133

134

135

ˆ

136

137

Š

138

139

Œ

140

Ž

142

145

146

147

148

149

150

151

˜

152

153

š

154

155

œ

156

ž

158

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s