Sort Order Question  
Author Message
travislspencer





PostPosted: Thu May 19 13:51:47 CDT 2005 Top

SQL Server >> Sort Order Question

We are in the middle of migrating some database from SQL 7.0 to 2000 and have
just found that we have a mismatch in "Sort Order" settings between the two
servers. The 7.0 has the Sort order of 53 and 2000 has 52. We need to match
the 2000 server (52) to 53.

My question is what are my options at this point. Our software vendor
suggested that we wipe out everything and start over (re-install SQL and go
through 3 days worth of data conversion) as the only option. Will backing up
the databases the we currently have on 2000, re-install SQL with the Sort
order=53, and reload databases work? What are the consequences?

Thank you in advance for any suggestions/recommendations.

Nick P.

SQL Server290  
 
 
David





PostPosted: Thu May 19 13:51:47 CDT 2005 Top

SQL Server >> Sort Order Question
> We are in the middle of migrating some database from SQL 7.0 to 2000
> and have just found that we have a mismatch in "Sort Order" settings
> between the two servers. The 7.0 has the Sort order of 53 and 2000
> has 52. We need to match the 2000 server (52) to 53.
>
> My question is what are my options at this point. Our software vendor
> suggested that we wipe out everything and start over (re-install SQL
> and go through 3 days worth of data conversion) as the only option.
> Will backing up the databases the we currently have on 2000,
> re-install SQL with the Sort order=53, and reload databases work?
> What are the consequences?
>
> Thank you in advance for any suggestions/recommendations.
>
> Nick P.

You can change the collation of a database using:
ALTER DATABASE MyDatabase COLLATE French_CI_AS

But it won't affect existing columns in user-defined tables. You can use
ALTER TABLE for that.

My guess here is that if you run an unsupported collation for your
vendor's application, you might not get support. I might bite the bullet
and rebuild the master database and recreate the user databases.


--
David Gugick
Imceda Software
www.imceda.com

 
 
NickP





PostPosted: Thu May 19 16:02:01 CDT 2005 Top

SQL Server >> Sort Order Question David:

Thanks so much for your response and advice.

The only difference that I can tell between Sort order 52 and 53 is that
Sort order 53 has 'Uppercase Preference". Do you know if SQL actually
stores something different in the tables or does it just use this during the
handling of sort process?

Thanks




> > We are in the middle of migrating some database from SQL 7.0 to 2000
> > and have just found that we have a mismatch in "Sort Order" settings
> > between the two servers. The 7.0 has the Sort order of 53 and 2000
> > has 52. We need to match the 2000 server (52) to 53.
> >
> > My question is what are my options at this point. Our software vendor
> > suggested that we wipe out everything and start over (re-install SQL
> > and go through 3 days worth of data conversion) as the only option.
> > Will backing up the databases the we currently have on 2000,
> > re-install SQL with the Sort order=53, and reload databases work?
> > What are the consequences?
> >
> > Thank you in advance for any suggestions/recommendations.
> >
> > Nick P.
>
> You can change the collation of a database using:
> ALTER DATABASE MyDatabase COLLATE French_CI_AS
>
> But it won't affect existing columns in user-defined tables. You can use
> ALTER TABLE for that.
>
> My guess here is that if you run an unsupported collation for your
> vendor's application, you might not get support. I might bite the bullet
> and rebuild the master database and recreate the user databases.
>
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
>