SQL Server 2000 suffers from a major problem when it comes to collation. It's a one-way street for every textual column in your databases. The following steps have worked for me at converting a large databases from one collation to another without having to BCP or create a new database, move data around or change individual columns.
WARNING: ANY CLAIMS, LOSSES, DEMANDS OR DAMAGES OF ANY KIND WHATSOEVER WITH RESPECT TO THE FOLLOWING INFORMATION INCLUDING BUT NOT LIMITED TO DIRECT, INDIRECT, INCIDENTAL OR CONSEQUENTIAL LOSS OR DAMAGES, COMPENSATORY DAMAGES OR LOSS OF PROFITS OR DATA WHETHER BASED ON A BREACH OF CONTRACT OR WARRANTY, DELICT OR TORT (INCLUDING NEGLIGENCE), PRODUCT LIABILITY ARE TOTALLY YOUR OWN RISK.
With that said, lets go over the ground rules:
- DO NOT PERFORM THIS ON A PRODUCTION ENVIRONMENT - If you do, you are an idiot
- DO NOT PERFORM THIS WITH OUT A SOLID BACKUP
- Do not simply verify your BACKUP files, restore to another name and
perform these actions on the copy database...use the two to compare
data with a third-party data comparison tool - DO NOT BLAME ME IF THIS DOES NOT WORK - If you really want a solution, call Micrsoft...enjoy their answer
- DO RUN A DBCC CHECKDB BEFORE AND AFTER - If you have bad data before this, it will be no better after
- DO NOT DO THIS TO A SYSTEM DB - That would be stupid.
- DO NOT PREFORM THIS ON DIFERENT CODE PAGES
- While most this will interop between most ansi/latin1 code pages, do
not try going from Latin1_General_BIN to Chinese_PRC_BIN...the words,
lost in translation come to mind.
On with the show...
1) DBCC CHECKDB
Make sure your existing physical data structures are consistent before you start
2) BACKUP and RESTORE to another database name
Do all of this work in the copy so if you know what hits the fan, you can recover without a restore.
3) Run rebuildm
An annoying problem fixed in
2005, the creation of any temp tables (eg. SELECT INTO,ect) will use the system databases collation, not the current database. In the real world, with SQL 2000, all databases should really be the same collation as the system db's for ease of use. This is where rebuildm comes in.
Rebuildm will blow away all of the configured settings, DTS, SQL agent jobs, proxy account, Maintenance plans, logins, and all other information stored in master, msdb, model, tempdb (duh). You will also want to script off reattaching both the old and the new databases. Run rebuildm is usually located under C:\Program Files\Microsoft SQLServer\80\Tools\Binn\. For safe measure, after rebuidm I like to rerun the latest service pack and hotfixes again to ensure all the system tables are correct after the rebuildm.
4) Script off all indexes, foreign keys
After
your system collation is set, you recreated all logins, dts, and reattached the databases, you need to script off all indexes and referential integrity from within your copy database. Reason being, all indexes need to be recreated (not simply rebuild with DBCC commands). Otherwise, they don't seem to pickup on the collation change. Do this however you like. I find it easiest to use Enterprise Manager and run the 'Generate SQL Script...' on all tables, making sure to select the indexes and primary, foreign key options. This will produce a script with DROP TABLE and CREATE TABLE commands. Using a text editor, remove everything except the CREATE INDEX statements.
Modify all of the CREATE INDEX statements adding 'WITH DROP_EXISTING' before the 'ON [FILEGROUP]'. Use some fancy regex statements in something other than notepad. Be cautious of finer grained index options (fill factor, ect)...these might not be retained with this
method.
5) Change the default database collation
ALTER DATABASE dbname COLLATE new_collation_name
This sets the system table's collation and the new database default collation for future columns.
6) Update syscolumns with the new collation
This is the most dangerous step because you are going to directly update the syscolumns table...do not do this on a production system, or any place where any data loss is unacceptable. REPEAT...IF YOU DO ANY OF THIS, YOU DO SO AT YOUR OWN RISK, YOU HAVE BEEN WARNED. Back to business...the first step is to find the collationid for the old and the new collations. Run the following query:
SELECT collation, collationid, count(*) AS count FROM syscolumns GROUP BY collation
This will product something like the following:
collation collationid count ----------------------- ----------- ------------ Latin1_General_BIN 65544 53 SQL_Latin1_General_CP1_CI_AS 872468488 104452
Assume that we want to go from SQL version of 1252 cp, case insensitive, accent sensitive, to windows 1252 cp, binary. The table with a single text column we created produced the new collection with a collationid of 65544 in this example.
With the IDs in hand, update them below and run:
sp_configure 'allow updates', 1 GO RECONFIGURE WITH OVERRIDE GO UPDATE syscolumns SET collationid = 65544 -- Latin1_General_BIN WHERE collationid = 872468488 -- SQL_Latin1_General_CP1_CI_AS sp_configure 'allow updates', 0 GO RECONFIGURE GO
This will change all columns that have the old collationid to be the new Latin1_General_BIN. Again, do not perform this any of these steps if you are trying to perform this on unlike codepages. How can you be sure they won't go wrong? All depends...try it and see. If you are
simply changing sorting options (eg. Binary, AS, CI) or collation version (eg. SQL to Windows) this should work like a charm.
7) Run the CREATE INDEX WITH DROP_EXISTING for all indexes
You will also want to restore all your referential integrity afterwords. Indexes don't seem to pick up the new collation until you have created them (a DBCC DBREINDEX will not work from my experience)
8) Run UPDATE STATISTICS on all tables
You just recreated all the indexes in your database, make sure the compile has good stats for those fresh smelling indxes.
9) Recompile all views and stored procedures
Sort of redundant with step 10 if you restart.
10) Restart SQL server or run DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE
Enjoy your new collation, or your catastrophe. You have been warned.