Chose MSSQL collation

hi,

I want to migrate from MYSQL to MSSQL 2005. My website Contains Multilangugal contents "Frace & Arabic". I 'm using "UTF_8 generalCI" Collation in MYSQL . What Collation I should use in MSSQL 2005. I only want to have One DB.

thanks,

Inorder to store multi-lingual text, you need to set the field datatype to NVARCHAR. This should work fine. If you need more information, let me know. I am currently working on an arabic web portal.

Thanks

|||

thanks for your Rply,

I've set field types as nvarchar. But If I set collation as "latin1swedish" and field as nvarchar then I can't find Arabic words.. would you please tell me, what is the role of collation in DB. suppose

would you please tell me what method are you using for Arabic website developement.Do you use 2 Seprate DB? hae you create arabic section as a real subweb or you use methods like profile.

best regards,

|||

Hi,

The collation is used when sorting and querying. When sorting, SQL Server will sort according to the selected collation.

You can alter a column's collation by using the COLLATE clause. Please check the following link for more information.

http://msdn2.microsoft.com/en-us/library/ms184391.aspx

|||thanks Kevin for your reply,If I've understood right, I could enter multi languagual contents to NVARCHAR fields, and Collation doesn't matter while save data.We could change the DB Colation later if needed.But what is the usual Collation. ex. what www.asp.net uses for it's collation. regards,

choosing a collation in SQL 2005

I have been searching for advice on the 'right' collation to choose in SQL 2005. I am US based and can assume any language required not supported by Latin1_General will be in unicode. I have SQL 2000 servers using SQL_Latin1_General_CP1_CI_AS and SQL_Latin1_General_CP850_CI_AS. The ones using CP850 will be converted to whatever collation is chosen in SQL 2005. I do not know the implications of choosing the default SQL collation as opposed to choosing Latin1_General_CI_AS. I have seen warning messages when installing analysis services if the SQL_Latin1_General_CP1_CI_AS is chosen saying that analysis services will use the colaltion Latin1_General_... . I also use non-updating subscribers in transactional replication currently in teh SQL 2000 environment and will continue to use replication in SQL 2005.

What are the implications of choosing the windows vs. SQL collation if the choice is between SQL_Latin1_General_CP1_CI_AS and Latin1_Gernal_CI_AS ? I do not want a collation mixture like I currently have in SQL 2000.

There's a helpful topic in Books Online that addresses this:

http://msdn2.microsoft.com/en-us/library/ms144260.aspx

Buck Woody

|||Thank you very much. This is the information I needed to see. I am not sure why searching BOL myself did not turn up this information.|||

If I am doing a fresh installation of SQL 2005 for a new application which will utilize databse engine, report server and analysis services is my best choice Latin_General_BIN2?

Thank you,
Alex Ivanoff

|||My understanding is binary collations do not sort in order you may expect. I'm not sure it's the best unless you know you need it. But, I'm not expert and am interested in an experts opinion.

choosing a collation in SQL 2005

I have been searching for advice on the 'right' collation to choose in SQL 2005. I am US based and can assume any language required not supported by Latin1_General will be in unicode. I have SQL 2000 servers using SQL_Latin1_General_CP1_CI_AS and SQL_Latin1_General_CP850_CI_AS. The ones using CP850 will be converted to whatever collation is chosen in SQL 2005. I do not know the implications of choosing the default SQL collation as opposed to choosing Latin1_General_CI_AS. I have seen warning messages when installing analysis services if the SQL_Latin1_General_CP1_CI_AS is chosen saying that analysis services will use the colaltion Latin1_General_... . I also use non-updating subscribers in transactional replication currently in teh SQL 2000 environment and will continue to use replication in SQL 2005.

What are the implications of choosing the windows vs. SQL collation if the choice is between SQL_Latin1_General_CP1_CI_AS and Latin1_Gernal_CI_AS ? I do not want a collation mixture like I currently have in SQL 2000.

There's a helpful topic in Books Online that addresses this:

http://msdn2.microsoft.com/en-us/library/ms144260.aspx

Buck Woody

|||Thank you very much. This is the information I needed to see. I am not sure why searching BOL myself did not turn up this information.|||

If I am doing a fresh installation of SQL 2005 for a new application which will utilize databse engine, report server and analysis services is my best choice Latin_General_BIN2?

Thank you,
Alex Ivanoff

|||My understanding is binary collations do not sort in order you may expect. I'm not sure it's the best unless you know you need it. But, I'm not expert and am interested in an experts opinion.

chinese full-text search

Hello,
Request your help on querying a Fulltext indexed table with Chinese Text.
I have sql server 2000, and a database with Chinese collation, a table is
full text indexed with Chinese(RPC) word breakers.
I am able to retrieve the Chinese text from the table and successfully
display the same on the browser but am not able to search.
What is happening is when I run the below query in query analyzer, it is
returning me a result set
SELECT * from DCNews_Live where contains (*,' "chinese text" ')
Where as when I run the same query from ASP (Active Server Pages) it is not
returning any values.
Can u explain where I am going wrong?
The ASP code that I use is
strSearch = trim(request.Form("txtSearch"))
strSql = "SELECT * from Table where contains(*,' """ & strSearch
& """ ')"
objRs.Open strSql,objCon
The Recordset is empty.
Can u please help out of this
Regards,
Prudhvi Raju M
Did you set your session code page for Chinese simplified or Traditional?
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Prudhvi" <Prudhvi@.discussions.microsoft.com> wrote in message
news:0A2D4FD7-8E9F-4C6C-BC5E-D0CA84B6E284@.microsoft.com...
> Hello,
> Request your help on querying a Fulltext indexed table with Chinese Text.
> I have sql server 2000, and a database with Chinese collation, a table is
> full text indexed with Chinese(RPC) word breakers.
> I am able to retrieve the Chinese text from the table and successfully
> display the same on the browser but am not able to search.
>
>
> What is happening is when I run the below query in query analyzer, it is
> returning me a result set
>
> SELECT * from DCNews_Live where contains (*,' "chinese text" ')
>
> Where as when I run the same query from ASP (Active Server Pages) it is
> not
> returning any values.
>
> Can u explain where I am going wrong?
>
> The ASP code that I use is
>
> strSearch = trim(request.Form("txtSearch"))
>
> strSql = "SELECT * from Table where contains(*,' """ &
> strSearch
> & """ ')"
>
> objRs.Open strSql,objCon
>
> The Recordset is empty.
>
> Can u please help out of this
>
> Regards,
> Prudhvi Raju M
>
>
|||I've haven't set the session code page but included the meta tag
<META http-equiv=Content-Type content="text/html; charset=windows-1252">
"Hilary Cotter" wrote:

> Did you set your session code page for Chinese simplified or Traditional?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> Now available for purchase at:
> http://www.nwsu.com/0974973602.html
> "Prudhvi" <Prudhvi@.discussions.microsoft.com> wrote in message
> news:0A2D4FD7-8E9F-4C6C-BC5E-D0CA84B6E284@.microsoft.com...
>
>
|||You need to set the code page in your asp page.
Run profiler to see the command that hits SQL Server. I think what is
happening is that the chinese characters being entered on your web server
are not making to SQL Server as Chinese characters, rather their character
representations in the 1252 code page. Hence no hits
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Prudhvi Raju" <Prudhvi Raju@.discussions.microsoft.com> wrote in message
news:8B136C72-FD83-4BF8-8A59-C5B263294E46@.microsoft.com...[vbcol=seagreen]
> I've haven't set the session code page but included the meta tag
> <META http-equiv=Content-Type content="text/html; charset=windows-1252">
> "Hilary Cotter" wrote:
|||Thanks Hilary for the advice.
I've included BIG5 codepage and also changed the meta tag to
<META http-equiv=Content-Type content="text/html; charset=big5">
and am now able to get the result set for chinese search.
Thank You.
Regards,
Prudhvi
"Hilary Cotter" wrote:

> You need to set the code page in your asp page.
> Run profiler to see the command that hits SQL Server. I think what is
> happening is that the chinese characters being entered on your web server
> are not making to SQL Server as Chinese characters, rather their character
> representations in the 1252 code page. Hence no hits
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> Now available for purchase at:
> http://www.nwsu.com/0974973602.html
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> Now available for purchase at:
> http://www.nwsu.com/0974973602.html
> "Prudhvi Raju" <Prudhvi Raju@.discussions.microsoft.com> wrote in message
> news:8B136C72-FD83-4BF8-8A59-C5B263294E46@.microsoft.com...
>
>

chinese data

please can you tell how you managing database for chinese

do we need to specify collation even if we use utf-8

how to use utf-8

thanks in advance

You should keep the data types as nvarchar
and while inserting the row in the database through query use the character N

e.g.

INSERT INTO TABLE_NAME (FIELD_1, FIELD_2) VALUES (N'VALUE 1' , N'VALUE 2')\

the character N and then the value in the single quotes if text

regards

|||

thanks i need small infoon whether we need to specify collation type

|||

Hi,

Right. You may set collations at the Server, Database, Column or Expression level of a SQL Server 2005 instance. For the collation name, you may refer the following link.

http://msdn2.microsoft.com/en-us/library/ms143508.aspx

Thanks.