Prev: Large Log File
Next: Database-Level Roles documentation in BOL
From: JimLad on 11 Aug 2010 09:29
Hi,

We are upgrading from SQL2000 to SQL2008R2. We are taking the
opportunity to update the collation on our databases.

Sorry to bring this up again but there are a lot of partial answers
online and a lot of conflicting information. Our situation is that we
have a database with NO COLLATE clauses, i.e. all columns use the
database default (temp tables excepted).

The only Microsoft knowledge base article on this seems to be from 7.0
and 2000. I wonder if the same comments still apply.
http://support.microsoft.com/kb/325335

So which is the best method (i.e. the simplest/fastest 100% correct
method) for updating the database collation and all data, given the
above details? The old collation is SQL_Latin1_General_CP1_CI_AS and
the new one will be Latin1_General_CI_AS.

As far as I can work out the options are:
1) A dynamic script that uses ALTER TABLE ALTER COLUMN statements on
an inplace database.
2) Create a new db with the correct collation - use SSIS to import
data and schemas.
3) Use SQL Compare.

Any advice very welcome.

Cheers,

James
From: Erland Sommarskog on 11 Aug 2010 17:13
JimLad (jamesdbirch(a)yahoo.co.uk) writes:
> As far as I can work out the options are:
> 1) A dynamic script that uses ALTER TABLE ALTER COLUMN statements on
> an inplace database.

Which is more difficult than it sounds, because indexes, foreign keys and
constraints must be dropped. I think did this once, but that was a migration
database with no FKs.

> 2) Create a new db with the correct collation - use SSIS to import
> data and schemas.

This is what have done. Although we have all our scripts on version control,
so we don't have to rely on scripting. And we did not use SSIS, but we
use BCP for the task. (We also already had the tools for this.)

Whether you actually can do this with SSIS I don't know. You don't want
it to copy the collation of the source database...

I would say that if you don't have any textual source for your database,
that is the first thing you should address.

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: Large Log File
Next: Database-Level Roles documentation in BOL