January 16, 2008 at 11:38 am
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.
January 16, 2008 at 12:03 pm
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.
January 16, 2008 at 1:23 pm
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/
January 16, 2008 at 2:32 pm
Did you define right collation?
January 16, 2008 at 3:41 pm
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
January 17, 2008 at 6:26 am
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.
January 18, 2008 at 7:59 am
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