Showing posts with label types. Show all posts
Showing posts with label types. Show all posts

Thursday, March 29, 2012

Does SQL Server support all languages using Unicode

I have an international Internet business and I use SQL Server with the Unicode field types of nchar, nvarchar and ntext. I seem to have no problem with customers entering Slovakian, Slovenian, Romanian, English or German, but Hebrew and Japanese do not seem to work. The resulting data coming back from the database after adding a record is garbage.

I had the software email me the data immediately before adding the data to SQL Server. It was appearing correctly with respect to the foreign language character format. The software does the database add and then returns a list of records currently in that table. The information for Japanese or Hebrew entries is always a string of question marks, e.g. ?

I have been told that the only solution is individual databases for those languages. Is this true?SQL Server supports all languages using UNICODE. You don't need a separate database for each language.

There may be some issues with collation order if you have strings from different languages in the same column. You have to choose a particular collation for the column so indexing can work correctly. The collation you choose may give some unconventional ordering for sorted data in some languages because the conventional ordering differs from region to region. Sharp s, ?, is ordered the same as s in English, but not in German Phonebook, for example. You can specify a collation based on the UI locale for your result ordering in queries to overcome this.

The "?" problem generally happens when you convert UNICODE data to multi-byte using the incorrect locale. If your application must use multi-byte characters for some reason, make sure the locale matches the data's language. If your middle-tier and UI can use UNICODE data directly, its best to avoid the wide character to multi-byte and multi-byte to wide character conversions so this problem doesn't occur.|||You don't understand the problem

1.) The Internet is global in nature.

2.)I have 1 database

3.) data is entered from the contents of a form in a program

4.) customers can come from anwhere in the world. They speak many languages. They fill out the form in their native language (Hebrew, Japanese, etc.) The form contents write to the database.

5.) The database field types are nchar, nvarchar and ntext.

6.) the table has 10 fields. There are 15 tables. If these fields have to be replicated for each unique language, and we assume about 10,000 different languages in the world, the database will not fit on any known hard drive. It will require hundreds of programming hours to determine what language the input data is in and what set of table elements they should go to or be retrieved from (once we figure out what language the viewer uses).

7.) If all of the above is true, any new languages can not be used without prior setup.

8.) Why does MySQL handle this and not SQL Server?

We're not looking to translate anything. What we want is something eminently simple. Whatever they write, in whatever language, goes into the database. When that record is retrieved, whatever is in the record, in any language, is returned as data, unaltered.

There's no sorting and no reporting. The database holds the data and it's delivered when asked for.

Surely there is a solution here that doesn't involve something almost as complex as cloning humans.|||

You don't need to replicate your data for each language. You just need to make sure your application stack is using UNICODE strings from end-to-end, including the web forms that are displaying the data. You've already got the SQL Server end covered if you are using nothing but nchar, nvarchar, and ntext.

I saw the ? problem many, many times back when I was working on international programs that used ANSI strings. The system function that does the UNICODE-to-multi-byte translation uses question-marks for the characters it can't translate into the specified multi-byte locale. In my applications, the problem was never in the server, it was always in my application. Invariably, my application was trying to convert UNICODE data from the server into multi-byte (ANSI) characters in my application, but the locale I had specified for the translation was incorrect for the data, so there was no mapping from the UNICODE data to the ANSI locale. (For example, there is no representation for Japanese characters in the ANSI Hebrew code page.) I stopped seeing this problem once the applications I was working on used nothing but UNICODE in the database, database access layer, middle-tier, and the UI.

The next things I would check are whether you've specified UNICODE strings as the data types in your data access layer (ADO automatically converts between UNICODE and ANSI strings if you tell it you want ANSI strings for example) and whether the UI is using UNICODE or ANSI display controls. Those have been the areas where I've had the most trouble with globalized applications in the past.
I hope this helps,
Steve

Monday, March 19, 2012

does MSSQL have the ability to do "named row types?"

Hi all. We have a mix of informix and mssql server and I want to know
if something we do in informix has an analogous feature in MSSQL. We
can define a "row type" in informix, like so:

create row type name_1(fname char(20),lname char(20));

The when we create any table that includes a first and last name, we
do so using this row type like so:

create table sometable(name name_1, some column,...etc)

This allows us to set a standard for certain common fields and avoids
having different developers build the same type of field in more than
one way, different lengths, etc.

Is there a similar function in MSSQL server?sumGirl (emebohw@.netscape.net) writes:
> Hi all. We have a mix of informix and mssql server and I want to know
> if something we do in informix has an analogous feature in MSSQL. We
> can define a "row type" in informix, like so:
> create row type name_1(fname char(20),lname char(20));
> The when we create any table that includes a first and last name, we
> do so using this row type like so:
> create table sometable(name name_1, some column,...etc)
> This allows us to set a standard for certain common fields and avoids
> having different developers build the same type of field in more than
> one way, different lengths, etc.
> Is there a similar function in MSSQL server?

In SQL 2000, no.

In SQL 2005, which currently is in beta, you can define structured types
in CLR languages like Visual Basic or C#, but it's not really the same
thing as the Informafix feature. I assume that in the Informix case you
can still say:

SELECT fname FROM sometable WHERE ...

and thus refer to the individual columns directly. With a CLR type in
SQL2005 you would have to say:

SELECT name.fname FROM sometable WHERE ...

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

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

Sunday, February 19, 2012

Documenter

In Access you can analyze tables and use the documenter to
write out all of the tables with their permissions, column
names, data types etc. Is there a way to do this in SQL
Server?Hi Tammy
SQL 2000 and earlier does not have a tool like Access documentor.
You can run sp_help with a table name as a parameter and view the same
information. Information Schema Views might also be used to build your own.
If you want to save a copy of the scripts used to rebuild your objects, you
can script all the objects in a SQL Server database. (Right click on the
database, All Tasks, Generate SQL Script...)
Nick Schueler
| Content-Class: urn:content-classes:message
| From: "tammy" <tammy@.urban.csuohio.edu>
| Sender: "tammy" <tammy@.urban.csuohio.edu>
| Subject: Documenter
| Date: Fri, 20 Feb 2004 07:30:18 -0800
| Lines: 4
| Message-ID: <1310201c3f7c6$71e90df0$a101280a@.phx.gbl>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Thread-Index: AcP3xnHpbT3GZ9OEQZKEzULTHYNYMg==
| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| Newsgroups: microsoft.public.sqlserver.datawarehouse
| Path: cpmsftngxa07.phx.gbl
| Xref: cpmsftngxa07.phx.gbl microsoft.public.sqlserver.datawarehouse:18909
| NNTP-Posting-Host: tk2msftngxa09.phx.gbl 10.40.1.161
| X-Tomcat-NG: microsoft.public.sqlserver.datawarehouse
|
| In Access you can analyze tables and use the documenter to
| write out all of the tables with their permissions, column
| names, data types etc. Is there a way to do this in SQL
| Server?
|