Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SpikeMelbost
Starting Member
6 Posts |
 Posted - 2011-07-03 : 13:34:34
|
| Hi,I've read several solutions to similar issues, but none so far do what I require.I've got a database with column level collation that I want to set to the default database collation. I can do that for the majority of tables & columns, but have started to run into problems where indexes are making the collation change fail.I can use DROP INDEX to get rid of the index, and then (for the first problem at least) I can change the collation, but I then don't know what info I need (or how to get it) to recreate the index after.I have used ALTER TABLE ALTER COLUMN COLLATE to set the collation, but maybe there's a better way? I already found that I need to be carefully not to change the flag that allows a field to be NULL, and I'm not 100% sure I'm not in danger of inadvertently making some other change.to rewind a bit, my original problem was caused by having a local database with one collation and a remote one with another. I changed the collation of my local databases, but noticed this did not change the collation of the system databases. So I've changed the collation of my local installation using rebuilddatabase.I then realised that collation is set at column level, so created a test program in vc++ (using CDatabase & CRecordset) to change the collation of columns.I can change most columns, but not those used in an index. I can change them if I get rid of the index, but then I don't know how to get the index back (or more correctly which data to store & how to store it so I can recreate the index).Can anyone help? I'm starting to feel like I've wandered down a really nasty path. |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2011-07-03 : 13:44:42
|
A simple way is before dropping the index:right click on the index in object explorer and choose to create a create-script for that index.
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
SpikeMelbost
Starting Member
6 Posts |
Posted - 2011-07-03 : 14:02:40
|
| hi webfred,Thats sounds like a promising manual solution, but (forgive my naiivity) what do I then do with the script?If possible, would also be nice to have a generic sql solution so it can be done without manual intervention. |
|
|
|
SpikeMelbost
Starting Member
6 Posts |
Posted - 2011-07-07 : 09:51:36
|
| Thanks to WebFred I have finally got a database that appears to be working :)Using create-script and the execute I could manually drop the index, run the ALTER TABLE ALTER COLUMN COLLATE code then recreate the index.Although this was a bit too manually intensive for my liking (because I needed to perform this for 30 odd indexes and several databases), I took it a step further by saving the drop and create scripts to file, then using these as the basis for creating drop code and create code (CDatabase needs a slightly different format of code to the script) I could get my code to complete the collation change. Job done - but surely there must be an easier way!Thanks Webfred. |
|
|
|
|
|
|
|
|
How to change collation