September 30, 2004 at 10:00 am
The new guy at my old job installed SQL 2K onto a new server with a different sort order (default) than was used on his SQL 7 server (DOCS). Now when running reports he gets collation/sort order errors. But he didn't realize he had issues until a days worth of changes had been made in SQL 2K.
Also, his two databases in SQL 2 K show sort order = 0. Is this the default now for non-legacy dbs?
Can he do this to recover? Backup the data and logs of the activity for the one day on SQL 2K. Reinstall SQL 2K fresh (the rebuildm tool did not work for him alledgedly). Use the last backup from SQL 7 to add back his databases. Then reapply the logs from the day of activity on SQL 2K.
Is there a better way?
TIA
September 30, 2004 at 1:55 pm
You can't apply logs from one database to another, which is what you would be trying to do by applying the 2K logs to a fresh upgraded 7.0 db. You have a couple options. You can create a new database with the correct collation, bulk load the data in, delete the old database and rename the new one. OR, you can change the collation of the existing database:
This information from books online, see collations, changing...
You can also use the COLLATE clause on an ALTER DATABASE to change the default collation of the database:
ALTER DATABASE MyDatabase COLLATE French_CI_ASAltering the default collation of a database does not change the collations of the columns in any existing user-defined tables. These can be changed with ALTER TABLE.
There is more information in books online in this topic. I would highly recommend taking a look at it.
Steve
October 1, 2004 at 2:23 am
I agree with hoo-t
We had the same problem with one server and successfully rebuilt master and changed the collations of the database.
There is a bug with rebuildm, see
http://support.microsoft.com/default.aspx?scid=kb;en-us;273572
Far away is close at hand in the images of elsewhere.
Anon.
October 1, 2004 at 6:38 am
I went through this in some detail last year when I was producing a database upgrade/conversion tool for one of our products, and I managed to set the thing up that it worked doing a join across linked servers, of which one was SQL7 running an arbitrary collation and the other was SQL2000 running the desired collation. I also got it to work if I detached the SQL7 database, copied it across, and attached it to the SQL2000 database.
There is a major difference between SQL7 and SQL2000 regarding collations:
- In SQL7, the collation for all databases is set by the server installation.
- In SQL2000, the server installation only sets the default collation, and you can set/change the collation of everything from there on down -- per database, per table, per field, and even per comparison in a WHERE clause of a cross-database join, if you want to go that far!
It can be made to work, without having to destroy everything, throw away all the pieces, and start again.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply