Collation Order SQL2000->SQL2005

#1

Hi,

I've moving some databases from SQL 2000 to SQL 2005 (new master and
model). All our Windows servers are set to either English US or English
UK. When installing SQL 2005 on a Windows server set to English UK, it
asks for the collation order, and the default is Latin1_General.

Some of the databases I need to move from SQL 2000 may have originally
come from SQL 6.5 or 7.0; as I understand it, each database can also
have it's own collation order.

Will my databases migrate OK with the defaults, or do I need to look at
setting compatibility options?

--
Gerry Hickman (London UK)

#2

"Gerry Hickman" <gerry666uk (AT) newsgroup (DOT) nospam> wrote

Quote:

Hi,

I've moving some databases from SQL 2000 to SQL 2005 (new master and
model). All our Windows servers are set to either English US or English
UK. When installing SQL 2005 on a Windows server set to English UK, it
asks for the collation order, and the default is Latin1_General.

Some of the databases I need to move from SQL 2000 may have originally
come from SQL 6.5 or 7.0; as I understand it, each database can also have
it's own collation order.

Will my databases migrate OK with the defaults, or do I need to look at
setting compatibility options?

--
Gerry Hickman (London UK)

Hi Gerry

The database collation is listed as part of the status column when you do a
sp_helpdb. This can be different to the collation for the instance, which
can cause problems when using temporary tables and using them in queries
with existing tables, Therefore it is usually best to place safe and keep
everything conistent. To change a collation you will not only have to use
ALTER DATABASE to change the database collation, but each column which has
the old collation will have to be changed as well with ALTER TABLE. UDTs may
also need changing which you can't do if they are being used.

HTH

John

#3

"Gerry Hickman" <gerry666uk (AT) newsgroup (DOT) nospam> wrote

Quote:

Hi,

I've moving some databases from SQL 2000 to SQL 2005 (new master and
model). All our Windows servers are set to either English US or English
UK. When installing SQL 2005 on a Windows server set to English UK, it
asks for the collation order, and the default is Latin1_General.

Some of the databases I need to move from SQL 2000 may have originally
come from SQL 6.5 or 7.0; as I understand it, each database can also have
it's own collation order.

Will my databases migrate OK with the defaults, or do I need to look at
setting compatibility options?

--
Gerry Hickman (London UK)

Hi Gerry

The database collation is listed as part of the status column when you do a
sp_helpdb. This can be different to the collation for the instance, which
can cause problems when using temporary tables and using them in queries
with existing tables, Therefore it is usually best to place safe and keep
everything conistent. To change a collation you will not only have to use
ALTER DATABASE to change the database collation, but each column which has
the old collation will have to be changed as well with ALTER TABLE. UDTs may
also need changing which you can't do if they are being used.

HTH

John

#4

"Gerry Hickman" <gerry666uk (AT) newsgroup (DOT) nospam> wrote

Quote:

Hi,

I've moving some databases from SQL 2000 to SQL 2005 (new master and
model). All our Windows servers are set to either English US or English
UK. When installing SQL 2005 on a Windows server set to English UK, it
asks for the collation order, and the default is Latin1_General.

Some of the databases I need to move from SQL 2000 may have originally
come from SQL 6.5 or 7.0; as I understand it, each database can also have
it's own collation order.

Will my databases migrate OK with the defaults, or do I need to look at
setting compatibility options?

--
Gerry Hickman (London UK)

Hi Gerry

The database collation is listed as part of the status column when you do a
sp_helpdb. This can be different to the collation for the instance, which
can cause problems when using temporary tables and using them in queries
with existing tables, Therefore it is usually best to place safe and keep
everything conistent. To change a collation you will not only have to use
ALTER DATABASE to change the database collation, but each column which has
the old collation will have to be changed as well with ALTER TABLE. UDTs may
also need changing which you can't do if they are being used.

HTH

John

#5

"Gerry Hickman" <gerry666uk (AT) newsgroup (DOT) nospam> wrote

Quote:

Hi,

I've moving some databases from SQL 2000 to SQL 2005 (new master and
model). All our Windows servers are set to either English US or English
UK. When installing SQL 2005 on a Windows server set to English UK, it
asks for the collation order, and the default is Latin1_General.

Some of the databases I need to move from SQL 2000 may have originally
come from SQL 6.5 or 7.0; as I understand it, each database can also have
it's own collation order.

Will my databases migrate OK with the defaults, or do I need to look at
setting compatibility options?

--
Gerry Hickman (London UK)

Hi Gerry

The database collation is listed as part of the status column when you do a
sp_helpdb. This can be different to the collation for the instance, which
can cause problems when using temporary tables and using them in queries
with existing tables, Therefore it is usually best to place safe and keep
everything conistent. To change a collation you will not only have to use
ALTER DATABASE to change the database collation, but each column which has
the old collation will have to be changed as well with ALTER TABLE. UDTs may
also need changing which you can't do if they are being used.

HTH

John

#6

"Gerry Hickman" <gerry666uk (AT) newsgroup (DOT) nospam> wrote

Quote:

Hi,

I've moving some databases from SQL 2000 to SQL 2005 (new master and
model). All our Windows servers are set to either English US or English
UK. When installing SQL 2005 on a Windows server set to English UK, it
asks for the collation order, and the default is Latin1_General.

Some of the databases I need to move from SQL 2000 may have originally
come from SQL 6.5 or 7.0; as I understand it, each database can also have
it's own collation order.

Will my databases migrate OK with the defaults, or do I need to look at
setting compatibility options?

--
Gerry Hickman (London UK)

Hi Gerry

The database collation is listed as part of the status column when you do a
sp_helpdb. This can be different to the collation for the instance, which
can cause problems when using temporary tables and using them in queries
with existing tables, Therefore it is usually best to place safe and keep
everything conistent. To change a collation you will not only have to use
ALTER DATABASE to change the database collation, but each column which has
the old collation will have to be changed as well with ALTER TABLE. UDTs may
also need changing which you can't do if they are being used.

HTH

John

#7

"Gerry Hickman" <gerry666uk (AT) newsgroup (DOT) nospam> wrote

Quote:

Hi,

I've moving some databases from SQL 2000 to SQL 2005 (new master and
model). All our Windows servers are set to either English US or English
UK. When installing SQL 2005 on a Windows server set to English UK, it
asks for the collation order, and the default is Latin1_General.

Some of the databases I need to move from SQL 2000 may have originally
come from SQL 6.5 or 7.0; as I understand it, each database can also have
it's own collation order.

Will my databases migrate OK with the defaults, or do I need to look at
setting compatibility options?

--
Gerry Hickman (London UK)

Hi Gerry

The database collation is listed as part of the status column when you do a
sp_helpdb. This can be different to the collation for the instance, which
can cause problems when using temporary tables and using them in queries
with existing tables, Therefore it is usually best to place safe and keep
everything conistent. To change a collation you will not only have to use
ALTER DATABASE to change the database collation, but each column which has
the old collation will have to be changed as well with ALTER TABLE. UDTs may
also need changing which you can't do if they are being used.

HTH

John

#8

"Gerry Hickman" <gerry666uk (AT) newsgroup (DOT) nospam> wrote

Quote:

Hi,

I've moving some databases from SQL 2000 to SQL 2005 (new master and
model). All our Windows servers are set to either English US or English
UK. When installing SQL 2005 on a Windows server set to English UK, it
asks for the collation order, and the default is Latin1_General.

Some of the databases I need to move from SQL 2000 may have originally
come from SQL 6.5 or 7.0; as I understand it, each database can also have
it's own collation order.

Will my databases migrate OK with the defaults, or do I need to look at
setting compatibility options?

--
Gerry Hickman (London UK)

Hi Gerry

The database collation is listed as part of the status column when you do a
sp_helpdb. This can be different to the collation for the instance, which
can cause problems when using temporary tables and using them in queries
with existing tables, Therefore it is usually best to place safe and keep
everything conistent. To change a collation you will not only have to use
ALTER DATABASE to change the database collation, but each column which has
the old collation will have to be changed as well with ALTER TABLE. UDTs may
also need changing which you can't do if they are being used.

HTH

John

#9

"Gerry Hickman" <gerry666uk (AT) newsgroup (DOT) nospam> wrote

Quote:

Hi,

I've moving some databases from SQL 2000 to SQL 2005 (new master and
model). All our Windows servers are set to either English US or English
UK. When installing SQL 2005 on a Windows server set to English UK, it
asks for the collation order, and the default is Latin1_General.

Some of the databases I need to move from SQL 2000 may have originally
come from SQL 6.5 or 7.0; as I understand it, each database can also have
it's own collation order.

Will my databases migrate OK with the defaults, or do I need to look at
setting compatibility options?

--
Gerry Hickman (London UK)

Hi Gerry

The database collation is listed as part of the status column when you do a
sp_helpdb. This can be different to the collation for the instance, which
can cause problems when using temporary tables and using them in queries
with existing tables, Therefore it is usually best to place safe and keep
everything conistent. To change a collation you will not only have to use
ALTER DATABASE to change the database collation, but each column which has
the old collation will have to be changed as well with ALTER TABLE. UDTs may
also need changing which you can't do if they are being used.

HTH

John

#10

"Gerry Hickman" <gerry666uk (AT) newsgroup (DOT) nospam> wrote

Quote:

Hi,

I've moving some databases from SQL 2000 to SQL 2005 (new master and
model). All our Windows servers are set to either English US or English
UK. When installing SQL 2005 on a Windows server set to English UK, it
asks for the collation order, and the default is Latin1_General.

Some of the databases I need to move from SQL 2000 may have originally
come from SQL 6.5 or 7.0; as I understand it, each database can also have
it's own collation order.

Will my databases migrate OK with the defaults, or do I need to look at
setting compatibility options?

--
Gerry Hickman (London UK)

Hi Gerry

The database collation is listed as part of the status column when you do a
sp_helpdb. This can be different to the collation for the instance, which
can cause problems when using temporary tables and using them in queries
with existing tables, Therefore it is usually best to place safe and keep
everything conistent. To change a collation you will not only have to use
ALTER DATABASE to change the database collation, but each column which has
the old collation will have to be changed as well with ALTER TABLE. UDTs may
also need changing which you can't do if they are being used.

HTH

John