SEO

Greetings,

I have a SQL server 2000 running on an english win2000 workstation. In a
database I have a table where one varchar column is set to polish
collation.
Regional settings for the system is polish.
Data entered in a client application looks fine until they are posted.
When reading the data with the client application, the special polish
characters are incorrect, they appears as e.g. '1' and '3'.
The strange thing is that when I use query analyzer to look at the data,
then the polish characters appears as they should!
My client app use ADO, the SQLOLEDB provider. I have tried to use
'Locale Identifier=xxxx' in the connection string, without any luck.
If I change the column to be nvarchar instead of varchar, then it work,
but unfortunately, this solution is not an option. This should work on
varchar columns, since polish is not multibyte.

What am I doing wrong??

TIA
Best regards
Philip Kofoed

Philip Kofoed (kofoed@tiscali.dk) writes:
> I have a SQL server 2000 running on an english win2000 workstation. In a
> database I have a table where one varchar column is set to polish
> collation.
> Regional settings for the system is polish.
> Data entered in a client application looks fine until they are posted.
> When reading the data with the client application, the special polish
> characters are incorrect, they appears as e.g. '1' and '3'.
> The strange thing is that when I use query analyzer to look at the data,
> then the polish characters appears as they should!
> My client app use ADO, the SQLOLEDB provider. I have tried to use
> 'Locale Identifier=xxxx' in the connection string, without any luck.
> If I change the column to be nvarchar instead of varchar, then it work,
> but unfortunately, this solution is not an option. This should work on
> varchar columns, since polish is not multibyte.

You say that the regional settings of the system are Polish, but which
system are you talking about?

What are the regional settings of the machine where the client application
runs? If that machine has for instance Danish settings, the Polish
characters will indeed be converted.

Could you give more examples on how the various Polish characters are
displayed as? How is a-ogonek displayed, c-acute, etc?

--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Erland Sommarskog wrote:

> You say that the regional settings of the system are Polish, but which
> system are you talking about?

The PC where _both_ the sql server and the client app runs, client app is
running on the same PC as the sql server. Windows is english, but with polish
regional settings. Collation of the varchar column where I'm saving the polish
string is polish.


> Could you give more examples on how the various Polish characters are
> displayed as? How is a-ogonek displayed, c-acute, etc?

I have placed some screenshots here:
http://www.provosoft.com/sql/sql.html

Data are stored correct, since query analyzer reads and displays data correct.
So the problem must be somewhere else.

Best regards
Philip Kofoed

Philip Kofoed (kofoed@tiscali.dk) writes:
> The PC where _both_ the sql server and the client app runs, client app
> is running on the same PC as the sql server. Windows is english, but
> with polish regional settings. Collation of the varchar column where I'm
> saving the polish string is polish.

But is Polish the default locale for the server? Or is it just the
setting for the user you are logged in as?

And what value do you have in
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Contro l\Nls\CodePage\ACP?
Does it say 1252 or 1250?

Looking at the examples, it seems clear that when the data is received
from SQL Server is taken to be Latin-1 data, and then there is a
conversion to CP1250, using fallback characters. For instance L-slash
is 163, which is the pound sign in Latin-1, whence the L. l-slash is
179 in Latin-2, while in Latin-1 this code point is 3-superscript, so
3 is used as the fallback.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

> But is Polish the default locale for the server? Or is it just the
> setting for the user you are logged in as?

Default locale is Polish. 'Language settings for the system' set to Central
Europe (default), 'Settings for current user' set to Polish locale.

> And what value do you have in
> HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Contro l\Nls\CodePage\ACP?
> Does it say 1252 or 1250?

1250, i.e eastern europe.

I just tried running my client app on a polish win XP. Client app then
connects to sql server running on english win 2000, default locale polish.
Characters are _still_ corrupted??
Query Analyzer still displays characters correct.

I'm going to leave this problem, and send it back the support staff. Thank you
for your time and effort, Erland!

Best regards
Philip Kofoed

Philip Kofoed (kofoed@tiscali.dk) writes:
>> But is Polish the default locale for the server? Or is it just the
>> setting for the user you are logged in as?
>
> Default locale is Polish. 'Language settings for the system' set to
> Central Europe (default), 'Settings for current user' set to Polish
> locale.

The last straw:

SELECT serverproperty('Collation'),
databasepropertyex('yourdb', 'Collation')

And exactly how do the queries submitted by the application look like?

I refuse to believe that the language of Windows should matter.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Hi Erland,

> I refuse to believe that the language of Windows should matter.

I have found out that if I create a new database with polish collation
then it works on english windows then regional settings is polish.
If collation of the DB isn't polish, but a column has polish collation,
then characters read from that column is incorrect.

Our installation crew that installed the original server in Poland,
installed it with Latin collation, realized their mistake, and used DTS to
copy the DB to a new DB with polish collation, and characters in the new
DB was dispalyed incorrect. I haven't tested this last scenario, because
our problem is solved when using polish collation when creating the DB.

Thanks again for all your help!

Best regards
Philip Kofoed

Philip Kofoed (kofoed@tiscali.dk) writes:
> I have found out that if I create a new database with polish collation
> then it works on english windows then regional settings is polish.
> If collation of the DB isn't polish, but a column has polish collation,
> then characters read from that column is incorrect.

Don't really see why this is happening if you are getting data directly
from the table. But if you for some reason first get data into local
variables, it's obvious, as local variables always have the collation
of the database.

Anyway, you got it working and that's the main thing.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp