We have recently updated our Business Objects installation with relatively good results. Our old setting was Data Services XI 3.1 (component version 21.1) running on Red Hat Enterprise Linux Server release 5.4 (Tikanga)
Our new setting is Data Services 4.1. running on Red Hat Enterprise Linux Server release 6.3 (Santiago)
Our only problem right now is with a batch job between Oracle and MS SQL. This worked fine with the previous setting and is running well enough with the new setting, with one problem: we are missing scandinavian characters (ä is now a etc.)
Source is Oracle 11.2 with locale:
SELECT * FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER LIKE '%ERSET';
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_CHARACTERSET AL32UTF8
Target is SQL Server 10.0 with database collation Finnish_Swedish_CI_AS_KS_WS and connected with ODBC driver DAsqls25.so
What I’ve tried so far:
Changing locales in Designer on both source and target databases. First with default, then both as utf8, then both as cp1252, then the source as utf8 and target as cp1252 etc. All reasonable combinations that I can think of.
Tweaking the ODBC connection(IANAAppCodePage)
Right now the odbc connection is looking like this
Hi,
Don’t know about the 4.0 (don’t have installed on my laptop yet) but with XI 3.2 you have a tool called “SAP BusinessObjects Data Services Locale Selector”.
I’m not sure if it overwritte the local defined in the designer/datastore… but this is an idea!
Thanks for the reply, tried reading the thread but found no advice to grasp.
Further question:
When I try running a test job and choose to print all trace messages I see that in the transform queries there are no special characters - ä is already a etc. at this point.
Does this mean that they are read from the source db incorrectly?
That either helped or created new problems - depends how you look at it
So writing to flat file shows that the characters are not OK at this point. So I did a test from another source table that has worked fine between Oracle and Oracle and the same happens.
It seems that when I run a job between Oracle and Oracle, some transformation is not done, (to the sources codepage?), but as soon as the target is different (SQL Server via ODBC, flat file) also the source is transformed.
On Windows the Oracle client it self has also a codepage setting. Hack in to the registry etc… but I have no clue at all how do this on a non-Windows platform.
Login to the DS user account in Linux, stop the jobservice. Then set the environment variable via export NLS_LANG=MAERICAN_AMERICA.AL32UTF8 and start the jobserver again.
Make sure you update the .profile for this user account as well
export NLS_LANG=MAERICAN_AMERICA.AL32UTF8
The codepage of the Oracle datastore should be UTF-8 now as well.
Thanks for the input Werner, we already had that option set. Any other ideas?
I tried to narrow down the problem and read a UTF8 flat file with special characters to target SQL Server and the characters were all in place.
At this point the target codepage and server codepage were set as default so it seems the problem is not in the ODBC connection or in that end rather than reading from Oracle.
So this is the situation:
Oracle → Oracle OK
Oracle → SQL Server NOT OK
Oracle → flat file NOT OK
flat file → SQL Server OK
You might be on to something, Werner! All other environment variables print out nicely with get_env, but NLS_LANG is null.
I must’ve done something wrong when updating the value? What I did was log on to server, change value and then start and stop. I’ll now try to first stop the server and then change file content somehow and then start.
EDIT: Same thing happened. I only restarted our AdaptiveJobServer - do I need to restart something else?
EDIT number 2: I did the starting and stopping from central management console
Simple solutions are the worst because they usually are not obvious. Oh dear, how often did I come up with something very cool just to get smacked over the head by somebody else saying “and why did you not click this button? Same result, much simpler?”
The database collation only sets the rules about how characters compare and sort against each other; it has nothing to do with how characters are stored in the database.
My recommendation would be to set the the database code page to be utf-8 and make sure all columns that can contain language text are nvarchar in the database tables. You will need to re-import the table meta-data - nvarchar is displayed in DS as varchar, but the imported table metadata is different. For template tables there is a setting “use nvarchar for varchar in supported databases” in the target table editor.
You also need to think about what tools you are using to view the data; e.g. if you’re using SSMS to run queries to check results you may need to set data grids to be displayed using a Unicode font.