Today I had the unpleasant task of changing the collation on several databases in a SQL 2005 environment. This essentially involves copying the data out, changing the collation of the underlying tables and then putting the data back again. Messy!
The ALTER DATABASE … COLLATE command gets you part way there. New objects will be created using the new collation, but what about all those object which were already present? Sadly they are not affected.
Luckily for me, Igor Kovalenko made this awesome script a few years back. It essentially moves your data out, changes the collation of the individual tables, and puts your data back where you expect it to be.
I then used RedGate SQLDataCompare to ensure that the data was consistent with a backup taken earlier on.
The only problem I hit with the script was with a VARCHAR(MAX) column. Changing the datatype to VARCHAR(8000) did the trick.
2 comments:
Hey, am I the first to leave a comment? :-)
Great post Ben! :-)
[url=http://www.cheapcanadagooseparkas.ca]canada goose expedition parka[/url] You can find the most expensive branded ones or you could find a cheap one. [url=http://www.busesbitermi.com]dr dre beats cheap[/url] Ukaxlm
http://www.christianlouboutindiscountsale.co.uk [url=http://www.ogrelarp.com]http://www.ogrelarp.com[/url] Qarpnc [url=http://www.pandorajewelryukonsale.co.uk]pandora jewellery[/url]
Post a Comment