collation

Question

Post reply

collation

bodhilove

SSCarpal Tunnel

Points: 4035
More actions
July 31, 2008 at 1:43 am

#190398

I am building a sql 2005 machine and restoring sql 2000 db on it and then replicating it with another sql 2000 machine offsite. I thought the best way to get the collation of the server was to look at the server I am restoring off (the 2000 box). SO I use select DATABASEPROPERTYEX(model,'collation') which I thought would give me default collation I also use SERVERPROPERTY(N'Collation') both tell me - Latin1_General_CI_AS
sp_helpsort tells me Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive. Regional Setting of my 2000 box are locale: English and Language: Western Europe United States.
I am guessing I need SQL collation but my problem is that most sql collations Latin1_General_CI_AS. Which one am I to pick?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Atif-ullah Sheikh SSChampion Points: 12495 More actions · Answer 1

R u having problem in data sort?

I think the default Collation is

SQL_Latin1_General_CP1_CI_AS

try query to check for your options...

SELECT Name, Description FROM fn_helpcollations()

WHERE name like 'L%'

SELECT Name, Description FROM fn_helpcollations()

WHERE name like 'SQL%'

Atif Sheikh

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.
You can check my BLOG [font="Arial Black"]here[/font][/url][/right]

bodhilove SSCarpal Tunnel Points: 4035 More actions · Answer 2

thanks. I think I have the right collation for my 2005 server - SQL_Latin1_General_CP437_CI_AS. SQL Server 2000 say Latin1_General_CI_AS though. Is this ok? What is the best way to verify it?

bodhilove SSCarpal Tunnel Points: 4035 More actions · Answer 3

Ok.. I will try to attack this from another viewpoint. My SQL server 2000 box say I am using Latin1_General_CI_AS server collation. I may be wrong but because this does not have SQL in front of it.. it seems like a windows collation. I thought SQL 2000 only had SQL collations not windows collations. Can you use a windows collation on a 2005 box if you are trying to restore a db from a 2000 box?

Atif-ullah Sheikh SSChampion Points: 12495 More actions · Answer 4

read the following article for your answers...

http://support.microsoft.com/kb/322112">

http://support.microsoft.com/kb/322112

I think it will give you a better understanding...

Atif Sheikh

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.
You can check my BLOG [font="Arial Black"]here[/font][/url][/right]

GSquared SSC Guru Points: 260824 More actions · Answer 5

bodhilove (8/2/2008)
Ok.. I will try to attack this from another viewpoint. My SQL server 2000 box say I am using Latin1_General_CI_AS server collation. I may be wrong but because this does not have SQL in front of it.. it seems like a windows collation. I thought SQL 2000 only had SQL collations not windows collations. Can you use a windows collation on a 2005 box if you are trying to restore a db from a 2000 box?

Different name, same collation.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon