SQL Server nvarchar, UTF-8 and French accents
Posted on 2014-01-14
I have a MS SQL Server 2005 database. The nvarchar fields are: SQL_Latin1_General_CP1_CI. I have a front end on the web (php) using these tables. I have English and French stored in database.
For some time now, I have been seeing occasional issues such as a 'black diamond with a question mark' appearing where an accented character should be or strange characters such as: 'FrÃ©deric' instead of 'Fréderic'. My web pages were encoded to ISO-8859-1. I changed them to UTF-8 as well as the php files querying the SQL Server database and updating the database. Everything seemed to be fixed as on the web it looked fine. However, today, when I wanted to run a report in SQL Server I found that every é I had updated to the database from the web (UTF-8) was stored as 'Ã©'.
Is there a way to cast the columns in SQL Server so the data displays properly when running a query in SQL Server (or a report in Access as the SQL Server DB is also connected to an Access project I have)? If I store it with correctly appearing accents in SQL Server then the data doesn't appear correctly on the UTF-8 charset web pages.
I have spent several hours on this and don't know what to do....