Foreign Language Names

  • I'm working with data with Polish and German names.

    I've defined my table columns as nvarchar (uni-code?).

    However, the names are not translating correctly.

    Table Column Values

    1. wˆasna Gdynia

    2. Szpital w Ostroˆ©ce

    3. Smyk w Pasa¾u Grunwaldzkim

    Desired Column Values

    1. własna Gdynia

    2. Szpital w Ostrołęce

    3. Smyk w Pasażu Grunwaldzkim

    Is there some database or server setting that I need to change?

    Thanks.

  • It would be helpful if you showed us the code you are using to insert into the tables, and the DDL statements for the tables.

  • somewhere at the back of my mind is a thought that you need a compatible character set for whatever client you are using to view the data - you may find sql server has stored the data correctly, you're just not able to display it correctly.

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • Did you define right collation?

  • Check in the data what ASCII numbers are for your characters.

    Here is a VBscript sample of how I check. The script is not perfect, it just loops through first 30 characters and post a message box with the number each time. Copy it to Notepad and save with VBS extension. The file myfile.txt with the text you are testing should exist in the same directory.

    What happens, if I run a query on nvarchar field and copy and paste a result in Notepad and name it myfile.txt and run my script, it shows non-unicode values. If I do Save Results As in Management Studio Query, by default it saves as Unicode and my script reads Unicode values. The difference is that Unicode values as read by the script have 255 and 254 in the beginning and then each letter with its ASCII number are followed by an empty message box because the Unicode takes 2 bytes. The non-Unicode values will be read as each message box will contain a letter and its ASCII number.

    First, you have to check if your ASCII numbers correspond to the correct letters in Polish Code Page. Second, check what is actually outputed by your Query Tool : Unicode or not

    Set objFSO = CreateObject("Scripting.FileSystemObject")

    Set objFile = objFSO.OpenTextFile("myfile.txt", 1)

    'Do Until objFile.AtEndOfStream

    I = 1

    while I<30

    strCharacters = objFile.Read(1)

    Wscript.Echo strCharacters & " " & Cstr(asc(strCharacters))

    I = I + 1

    ' Loop

    Wend

    Regards,Yelena Varsha

  • Thank you all for your help, and my apologies for not giving more background info.

    What I am doing is selecting data from Sybase tables via a linked server, and storing that data in SQL tables for reporting purposes. The linked server uses an odbc dsn with the 'Adaptive Server Anywhere 8.0' driver'.

    I ran a select query in Management Studio against a table in the linked server and had the same character translation problem.

    A different odbc dsn we use in MS Access for linked tables has the 'Character Set' in the advanced tab set to 'cp852'. This got me thinking that the problem is with the dsn, and not my SQL database or table.

    I did some quick Googling and learned that the Polish language uses code page 1250 (or cp1250). So, I changed the Character Set from cp852 to cp1250 in the advanced tab.

    The results are better, but not correct.

    What I get in SQL Server

    1. Pawel Ciezki

    2. Radoslaw Domaradzki

    What I get (correctly) in MS Access

    1. Pawel Ciezki

    2. Radoslaw Domaradzki

    So, I'm thinking I still have the wrong character set (cp1250) or the wrong database collation (Polish_CI_AS), or a combination of both.

    Can anyone suggest a character set and database collation combination that supports both the Polish and German languages?

    I have numerous queries. Here is an abbreviated sample.

    Select creator_id

    , order_no

    , created_on

    , followup_date

    , market_type

    , Convert(NVarchar(100),project_name) As project_name

    , Convert(NVarchar(50),project_state) As project_state

    , ship_country

    , order_rep

    Into #cs_project

    From eRapidInt..dba.cs_project

    Where project_name Not Like '%test%'

    And configured_price >= 1

    And order_no Like '%_00';

    A sample abbreviated table definition:

    CREATE TABLE [dbo].[tblMain_Detail](

    [CS_Country] [char](2) COLLATE Polish_CI_AS NULL,

    [Type] [char](1) COLLATE Polish_CI_AS NULL,

    [Status] [nvarchar](30) COLLATE Polish_CI_AS NULL,

    [Rep_id] [nvarchar](20) COLLATE Polish_CI_AS NULL,

    [Rep_Name] [nvarchar](50) COLLATE Polish_CI_AS NULL,

    [Quote_Number] [nvarchar](25) COLLATE Polish_CI_AS NULL,

    [Issue_Date] [datetime] NULL,

    [Expiration_Date] [datetime] NULL,

    [Lost_Reason] [nvarchar](255) COLLATE Polish_CI_AS NULL,

    [Create_Date] [datetime] NULL,

    [Follow_Up_Date] [datetime] NULL,

    [Line_Number] [nvarchar](25) COLLATE Polish_CI_AS NULL,

    [Market] [nvarchar](40) COLLATE Polish_CI_AS NULL,

    [Customer] [nvarchar](150) COLLATE Polish_CI_AS NULL,

    [Project] [nvarchar](100) COLLATE Polish_CI_AS NULL,

    [Quote_State] [nvarchar](50) COLLATE Polish_CI_AS NULL,

    [Quote_Country] [nvarchar](50) COLLATE Polish_CI_AS NULL,

    [Order_Rep] [nvarchar](50) COLLATE Polish_CI_AS NULL,

    [Model] [nvarchar](25) COLLATE Polish_CI_AS NULL,

    [Quantity] [float] NULL,

    [Product_Price] [float] NULL,

    [CS Product_Price] [float] NULL,

    ) ON [PRIMARY]

    Thanks.

  • I'm now getting correct Polish language translation after much trial and error with experimenting with dsn character set settings and collation settings at the instance, database, and column levels.

    There is a 4th collation setting in my case that I missed. It's on the linked server, in the options page.

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply