localization and sqlserver

[Justin]

Hi,

I have a simple 'user' table and an ASP form that connects to it. I
want users of all languages to be able to type in their registration
info, in their own language, store it that way, then have another page
that displays it.

Are there any specific settings I need in SQL Server to handle this?

Thanks

[Justin]

Re: localization and sqlserver

Sorry, I forgot to mention I'm using SQL Server 2000, Windows 2000
server and ASP Classic.

On Tue, 10 Aug 2004 19:25:34 GMT, Justin
<ng@NO_SPAMmari timeNO_SPAMsour ce.ca> wrote:
[color=blue]
>Hi,
>
>I have a simple 'user' table and an ASP form that connects to it. I
>want users of all languages to be able to type in their registration
>info, in their own language, store it that way, then have another page
>that displays it.
>
>Are there any specific settings I need in SQL Server to handle this?
>
>Thanks[/color]

[Erland Sommarskog]

Re: localization and sqlserver

Justin (ng@NO_SPAMmari timeNO_SPAMsour ce.ca) writes:[color=blue]
> I have a simple 'user' table and an ASP form that connects to it. I
> want users of all languages to be able to type in their registration
> info, in their own language, store it that way, then have another page
> that displays it.
>
> Are there any specific settings I need in SQL Server to handle this?[/color]

There are ways to set user languages in SQL Server, yes, but I am not
sure that this is what you are after. What happens when you set the language
in SQL Server is that it controls how date literals are interpreted, the
output from the function datename(), and it may also affect the text of
error messages. There are probably a few more things, but nothing terribly
exciting. And the format of dates is best handled client-side anyway.

Maybe you could clarify what you are looking for?

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Justin]

Re: localization and sqlserver

On Tue, 10 Aug 2004 21:45:58 +0000 (UTC), Erland Sommarskog
<esquel@sommars kog.se> wrote:
[color=blue]
>Justin (ng@NO_SPAMmari timeNO_SPAMsour ce.ca) writes:[color=green]
>> I have a simple 'user' table and an ASP form that connects to it. I
>> want users of all languages to be able to type in their registration
>> info, in their own language, store it that way, then have another page
>> that displays it.
>>
>> Are there any specific settings I need in SQL Server to handle this?[/color]
>
>There are ways to set user languages in SQL Server, yes, but I am not
>sure that this is what you are after. What happens when you set the language
>in SQL Server is that it controls how date literals are interpreted, the
>output from the function datename(), and it may also affect the text of
>error messages. There are probably a few more things, but nothing terribly
>exciting. And the format of dates is best handled client-side anyway.
>
>Maybe you could clarify what you are looking for?[/color]

I'm investigating strategies for internationaliz ation of an asp-based
website.

So basically as long as the sqlserver supports unicode I can stuff any
kind of characters in there I want to, but the only difference is when
using SQL Servers functions it has to know what locale it's dealing
with?

[Erland Sommarskog]

Re: localization and sqlserver

Justin (ng@NO_SPAMmari timeNO_SPAMsour ce.ca) writes:[color=blue]
> I'm investigating strategies for internationaliz ation of an asp-based
> website.
>
> So basically as long as the sqlserver supports unicode I can stuff any
> kind of characters in there I want to, but the only difference is when
> using SQL Servers functions it has to know what locale it's dealing
> with?[/color]

Of course the full story is not that simple. Generally, I would to as much
of the localization client-side, but there are of course situations when
the DB engine gets involved. And you may find these too complex to actually
resolve.

All character columns in SQL Server has a collation, but obviously one
collation that fits one user, does not fit another.

The most obvious case is sorting. While you can sort client side, it's
probably more performant to do it on the server. If you are sending down
SQL statements from the server, you can tack on a COLLATE clause:

SELECT *
FROM tbl
ORDER BY textcol COLLATE Finnish_Swedish _CI_AI

If you use stored procedures, you will have to resort to dynamic SQL, in
which case the point with the procedures are lost to a great deal.

When it comes to searching it becomes more delicate. Say that a user
is looking for a person named Wallenberg, and enters Vallenberg. In
most collations you would not get a hit, but in Finnish_Swedish _CI_AI
you would, since W is just a variant of V in Swedish. You can address
this with the COLLATE clause, but this will lead to indexes being
useless, and can have serious performance consequences. I would say
that in this case you will have to settle with Latin1_General_ CI_AI
or what you choose.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp