Problem with accents, charset, db mssql

[comas17]

Some days ago I have migrated my sugarcrm 5.2.0 from MySQL DB to MS SQL Server 2005.
I created the new database and migrated (with MS Integration Services) all the data from MySQL tables to MSSQl 2005 tables
Everything is working fine except for the accents (in italian we have a lot: à,è,é,ò,ì ... :-)
In my db tables data are correct; for example "caffè" (coffee) but in my sugar pages it appears as "caff" and a little square

If I insert a new data from sugar pages, for example "città" (town) it is displayed correctly but in my db table i find ""cittÃ"

I want to access to db tables also with other tools (ex. Reporting Services) so I need correct data

I found lots of forums and threads with "utf8" settings, "config.php" settings, "php.ini" settings but none seems to work

Does anybody can help me ?

SugarCRM 5.2.0 Community Edition
Windows 2003 Web Edition
SQL Server 2005 Standard Edition
PHP 5.2.5

Thank you

[andopes]

It is because the MySQL as SugarCRM works on UTF-8 but MS SQL not, only ISO-8859-1.
You need to convert all data into MS SQL from UTF-8 to ISO-8859-1.

Kind regards

[comas17]

Thank you andopes for your quick response
Can you explain me better ?
Do I need to convert all the data already stored in my MSSQL tables ? how ?
Or do I need to modify only some config file ?
I have already tried to change my config.php file to put 'default_charset' => 'ISO-8559-1' instead of 'default_charset' =>'UTF-8' but with no success



Thank you

[andopes]

Hi comas17

One solution is to convert the data into database.
You should need to export the database to a sql query and run some charset converter application.
Another solution, we used to solve another issue in a customer is to hack a single file into SugarCRM which make the system deal properly with data saved into ISO-8859-1 charset.

I have no the code right here.
Just send me an email remembering me and I´ll send you the file asap.

Kind regards

[comas17]

On another website I found informations about MSSQL, PHP, UTF8, UCS2... problems

(http://docs.moodle.org/en/Developmen...TF-8_and_UCS-2)

They suggest to use FreeTDS or ODBTP

Does SugarCRM support this possibility ? Does anybody have already tried it ?

Thank you

[comas17]

Anybody ?
Please...

Thank you

[andopes]

Hi comas17

FreeTDS only makes sense if you are using SugarCRM on Linux and a SQL Server database.

Cheers

[comas17]

Hi comas17

FreeTDS only makes sense if you are using SugarCRM on Linux and a SQL Server database.

Cheers

Ah, OK thank you
And what about ODBTP ? (I've seen that ODBTP is only for NT4.0, Win 2000 or XP and my server is Win 2003 ... )

(Can you please send me the file you mentioned in your previous message ? I sent an email to your company on 8/1/2009...)

Thank you again

[marketadvantage]

I am experiencing the exact same issue. I looked into using ODBTP, but we use win2003 and was unable to make it work. Any new thoughts on this? Thanks for the help!

[andopes]

Edit this file and copy the piece of code between "Lampada hack" into the original one into include/database/MssqlManager.php
This hack will convert SugarCRM data from UTF-8 to ISO-8859-1 and MS SQL Server data from ISO-8859-1 to UTF-8.
It does not convert the UTF-8 data already saved into your database, so you need some tool to translate the data stored into database.


Cheers