For databases that use Asian characters it will be necessary to make modifications to the standard Jet Enterprise project in order for the characters to be displayed properly in the data warehouse and staging databases.
Setting up the Assembly and Function
- Download the attached file named "CLR Function - Import.zip" from the Attachments section at the bottom of this article and unzip the contents into the Jet Data Manager folder (default: C:\Program Files\Jet Data Manager Server).
- Open “CLR Function - Import.sql” in SQL Server Management Studio
- Make sure the database context is set to your stage database.
- Copy the SQL script below (Step 11) and paste it into a new query window.
- If you have installed Jet Data Manager in the default path you can execute the query, otherwise you first need to update the file path to the DLL under the “Import CLR Assembly” section of the query.
Updating the Staging Database in the Jet Data Manager
Check the “Force Unicode Conversion” checkbox and click OK.
Right click the data source name again and select “Synchronize Objects”.
In your stage database, create a new custom table named “Parameters”.
Create a custom field in the table called “SourceCodePage” with the data type set to “Integer”.
Create a custom field in the table called “TargetCodePage” with the data type set to “Integer”.
Create a custom field in the table called “SourceDatabase” with the data type set to “Text” with the default text length.
Right click Parameters table and click Advanced > Custom Data.
Type in the Source Code Page and Target Code Page for each NAV adapter that you have connected to the project. Type in the NAV adapter name (default: NAV) in the SourceDatabase field.
For each text field in the staging database that needs to be converted you will need to right click on the field name and select “Field Transformations”. For the Operator select “Custom” and click Add. Insert the following SQL statement:
- Launch Jet Data Manager and open your project.
- Scroll down to Data Sources, right click on the data source name (default: NAV), and select “Edit Microsoft SQL Provider”.
- If connecting to a NAV Native database, select "Edit Navision Native Database" instead.
- If connecting to a NAV Native database, select the "Use Preset Settings" radio button and then select the appropriate version from the associated drop-down list.
- If this is the first time you are connecting to this data source, this will read as "Read Objects" instead of "Synchronize".
- dbo.ChangeEncoding( [Name] , (SELECT SourceCodePage FROM Parameters_V WHERE SourceDatabase = [DW_SourceCode]), (SELECT TargetCodePage FROM Parameters_V WHERE SourceDatabase = [DW_SourceCode]))
- NOTE: You need to change the highlighted field name to the actual field name you are performing this transformation on.
Updating the Data Warehouse in the Jet Data Manager
- For each text field that you have converted on the staging database you need to convert the field to Unicode on the Data Warehouse as well.
- Right click the field and select “Edit Field”.
- Check the “Unicode” checkbox and click OK.
- Deploy and execute the project.