Data Services 3.2 and SQL Server Collation 850

system · October 7, 2010, 12:48pm

Hello everyone

I have a problem when ready a table from SQL Server 2005 with collation CP850 some caracters are not well read.

I’ve tryed evereything ! changing all parameters in datastores etc…
and it seems that the only ay to do it is to change the collation of the DB

BUT does anyone knows a better way to do it ? ( I mean without changing the DB collation )

thank u for ur help

-L.

LSchmetz (BOB member since 2010-09-22)

system · October 7, 2010, 6:45pm

Need more input, I believe.

Does that help:
https://wiki.sdn.sap.com/wiki/display/BOBJ/Multiple+Codepages

Werner Daehn (BOB member since 2004-12-17)

system · October 8, 2010, 12:31pm

For instance I have this character in my source : Ä
DB source : SQL Server 2005
Collation : SQL_Latin1_General_CP850_CI_AS

and when I read this table from Data Services XI 12.2
it reads the character like this :

it’s like this collation is not suported by Data Services XI 12.2
but it works fine with Data integrator 11.5.3.9

hope it helps…

LSchmetz (BOB member since 2010-09-22)

system · October 8, 2010, 3:38pm

if it was working fine on 11.5.3.9 then it should work in DS 12.2

I can reproduce the issue, the loading is fine, I am able to load the Ä character correctly, view data shows it as
if I change the column datatype to NVARCHAR then it works fine in view data

I am creating a bug for this issue for further investigation

manoj_d (BOB member since 2009-01-02)

system · October 18, 2010, 10:00am

thank u for the investigation
but in my case the load is not correct
target database is : SQL Server 2005
collation: SQL_Latin1_General_CP850_CI_AS

it writes in the target.

Can u please give me the reference of the issue for further following ?

regards.

LSchmetz (BOB member since 2010-09-22)

system · October 18, 2010, 5:45pm

can you give me the following additional info for the bug
OS Locale
Locale setting in DsConfig.txt
Codepage of the Target SQL Server Datastore

I think the problem is in the reading/source only not with loading, is your source table also on SQL Server 2005, with same collation ? try a using a row gen as source and hardcode the data in the mapping, and check if you are able to load the data correctly

verify if the data is correct or not from SQL Server Query Analyzer

manoj_d (BOB member since 2009-01-02)

system · October 19, 2010, 9:44am

Codepage for source and target datastore are the same :

source and target DB are all : SQL server 2005 with collation
SQL_Latin1_General_CP850_CI_AS

I’ve made a rowgen on my target with character Ä and in SQL query analyser the result is correct however in the designer when I open the table I see

So it seems that the problem is from the reading part in the dataflow because it reads and not Ä so it writes

OS locale is set to english (united-states) and the DSConfig as well

regards

LSchmetz (BOB member since 2010-09-22)

system · October 20, 2010, 7:46am

Your SQL Server is using cp850, that means the hex code 0x8E represents the character Ä.

Your datastore is set to default, hence the OS codepage is used, as you are using a western european or US Windows version that is iso-8859-p1 or cp1252 to be more precise. There the character for hex 0x8E is a .

So change the datastore to cp850 and you are set. There is a value for that in the codepage list, is there?
If not, there was a bug in older releases.
Go to the DI installer directory, the DI\bin\DICodepages.txt. Find the text cp850. There is an entire block with different spellings and all are prefixed with -99. That needs to be changed to 850.

So the result should be

850  cp850    0
850  ibm-850  0
850  IBM850
...
...
850  windows-850   0

AND DON’T CHANGE THE LOCALE OF THE DSCONFIG, leave it to default!

Werner Daehn (BOB member since 2004-12-17)

system · October 20, 2010, 8:27am

Hello thank u for ur reply and ur help,

I’ve updated the DSCodepage.txt file like this

…
#------------------------------------------------------------------------------------------------------------------------------------------------------
850 ibm-850_P100-1995 0
850 ibm-850 0
850 IBM850 0
850 cp850 0
850 850 0
850 csPC850Multilingual 0
850 windows-850 0
#------------------------------------------------------------------------------------------------------------------------------------------------------
…

and the CP850 option doesn’t appear in the dropbox in the DataStore options
the only one that appear for (850 value ) is ibm-850_P100-1995

regards,

-L.

LSchmetz (BOB member since 2010-09-22)

system · October 20, 2010, 9:48am

errr ok

In fact I had to put CP850 at the top of the list in the file and it works now

…
#------------------------------------------------------------------------------------------------------------------------------------------------------
850 cp850 0
850 ibm-850_P100-1995 0
850 ibm-850 0
850 IBM850 0
850 850 0
850 csPC850Multilingual 0
850 windows-850 0
#------------------------------------------------------------------------------------------------------------------------------------------------------
…

thanks a lot for ur help !

regards.

LSchmetz (BOB member since 2010-09-22)

system · October 20, 2010, 1:57pm

Werner Daehn (BOB member since 2004-12-17)

system · October 20, 2010, 6:36pm

I can see this issue in 12.2.2.3 and XI 4.0 as well
have filed a bug for this in XI 4.0, once its fixed will have it scheduled for 12.x

manoj_d (BOB member since 2009-01-02)

system · October 21, 2010, 9:31am

By the way …can u tell me when XI 4.0 should be released
and what are the new features and upgrades ?

LSchmetz (BOB member since 2010-09-22)