French Text stored in SQL Server Appears Wrong, how to make it appear correctly

Question

I've got a table that is comprised of the following structure.

 CREATE TABLE [dbo].[tblData](
    [ID] [numeric](18, 0) NOT NULL,
    [QID] [varchar](25) NOT NULL,
    [Data] [nvarchar](255) NULL,
  CONSTRAINT [PK_tblData] PRIMARY KEY CLUSTERED 
   (
      [ID] ASC,
      [QID] ASC
   )
 ) ON [PRIMARY]

In the above example, the column [data] has a mix of different languages inside of it. We collect this data via a website where we set the appropriate code page. I understand that the data that was inserted in to our table is whatever the code page was set to on the webpage at the time the data was collected. We have this info stored elsewhere.

Every language that we are using has been coming out of the table fine. For example, Chinese comes out and displays as we need it to. We are trying to dump this data to a Unicode text file. To be specific: "UCS-2 LE" There is only one language that isn't coming out and that is French. For some reason the normally accented characters are coming out completely wrong. What I've found is that the data (I hope) appears to be "stored" as the wrong characters.

A colleague believes that this data is in the table probably correct and we just need to covert it to either to correct character set or the correct code page to display the data properly before we put the data in a UCS-2 LE text file.

Can anyone provide any insight in to my issue? Thank you!

Update: I found this: http://windowsitpro.com/windowsstorage/Article/ArticleID/14045/windowsstorage_14045.html - is this basically saying that what i need done can't be done? I think what I'd like to do is slightly different but that cause be [false-]hope.

Answer 1

Hi,

Your colleague is correct. As the data is stored in a UNICODE data type the native language format should be preserved.

What needs to happen is when you SELECT the data, you want to ensure that an appropriate collation is used for the user who executed the request i.e. their locale.

Consult the following Books Online reference for using collations in SQL Server

http://msdn.microsoft.com/en-us/library/ms144260(SQL.90).aspx

Hope this helps. Cheers.