change collation on a datase

Question

change collation on a datase

Viewing 15 posts - 16 through 30 (of 30 total)

You must be logged in to reply to this topic. Login to reply

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 1

noeld (1/15/2008)
This: http://support.microsoft.com/kb/325335 is the official way... funny it says the review happened in 2007 but it dos not mentions 2005 😉

In the beginning summary they mention that all concepts and discussion on 2000 also applies to 2005.

Which, in turn, mean that what this doc says is essentially what I first proposed.

data out - rebuild with proper collation - data in.

/Kenneth

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 2

Henrik,

it seems you have a bit of a mess regarding collations in db's vs server installations.

Consider that the choice of collation and sortorder is a fundamental piece of a system's install.

Getting these 'right' are essential for a long and troublefree duration of the system's life.

So, if I was in your shoes, I'd consider taking a step back, carefully evalute what collations are the correct ones for which db and server, and then bite the bullet and do it 'proper'.

That is, not an 'inplace' conversion, but rather a drop/uninstall recreate/install.

Keeping in mind that the default server collation preferrably should match (tempdb won't be an issue then etc) and so on.

Personally, this is my preferred approach to this kind of problem, just the same as I'm weary of upgrading windows from one version to another. I always prefer the 'clean install' approach before the 'upgrade'. Makes me sleep better 😉

In any case, I believe you need to think things over which method that would serve you best in the long run.

Regarding space, temp diskspace in particular, it's really cheap nowadays, so a TB or two shouldn't be a showstopper.

/Kenneth

Henrik Staun Poulsen SSCertifiable Points: 6578 More actions · Answer 3

Kenneth,

I work in a place with over 13000 employees, and 700+ h/w & s/w developers.

Naturally there are a lot of SQL installations.

The listed ones are just the ones that my manager is "responsible" for.

One of the products is installed in 150+ sites, so a few TB each place is not an option, as previously stated. Going round with USB disks for the upgrade is also a last resort. Some sites take 3-4 days journey to visit.

So if we "bite the bullet", we have time to investigate many options. This script is the one that involves the least effort from our part.

I hope that we can get it to work!

Thank you for your help.

Best regards,

Henrik

fhanlon SSChampion Points: 13131 More actions · Answer 4

look at the script http://www.sqlservercentral.com/scripts/Miscellaneous/61368/

as a tool that may help you. If the collation on a particular database is wrong it doesn't imply that all the tables have incorrect collation. Perhaps some have but maybe not all. This script may help identify what collation has been used.

HTH

Francis

Henrik Staun Poulsen SSCertifiable Points: 6578 More actions · Answer 5

Henrik Staun Poulsen

SSCertifiable

Points: 6578

January 18, 2008 at 2:57 am

#769581

Francis,

Thanks for reminding me.

Henrik

mark 17451 SSC Rookie Points: 30 More actions · Answer 6

Very nice Henrik. I have a need for this script as I had a client db with a different database collation to the table column collations. This as you can understand was causing problems. I have just run your script and so far it looks to have worked perfectly. I will continue testing this morning and if all ok will be running it on a live client database (after backing up of course!)

mark 17451 SSC Rookie Points: 30 More actions · Answer 7

Sorry I spoke to soon ... I ended up with 88 of these messages:

ALTER TABLE ALTER COLUMN PeMobileTel failed because one or more objects access this column.

Looks like the best way forward for me will be to rebuild the database

Irwan Tjanterik SSC-Addicted Points: 470 More actions · Answer 8

Henrik

Send me aur email, I'll send u an app I use to change collation.

Regards

IT

Henrik Staun Poulsen SSCertifiable Points: 6578 More actions · Answer 9

mark,

Is this not because you have a foreign key that you must disable first, before you can change the collation?

Then enable the FK afterwards, when the new collation is in place.

Best regards,

Henrik

Vrosenberg Grasshopper Points: 16 More actions · Answer 10

I know this is an old post, but as it is also a very useful one, I will add that I needed to add a short loop at the front to turn off all check constraints, and a short one at the end to re-enable them. One does not need to list each constraint, only the tables effected

select distinct 'ALTER TABLE .[dbo].['+rtrim(P.name) +'] CHECK CONSTRAINT all'

from [INFORMATION_SCHEMA].[CHECK_CONSTRAINTS] C

join sys.sysobjects O on name=CONSTRAINT_NAME

join sys.sysobjects P on P.id=O.parent_obj

and

select distinct 'ALTER TABLE .[dbo].['+rtrim(P.name) +'] NOCHECK CONSTRAINT all'

from [INFORMATION_SCHEMA].[CHECK_CONSTRAINTS] C

join sys.sysobjects O on name=CONSTRAINT_NAME

join sys.sysobjects P on P.id=O.parent_obj

Also, since most collation changes are "TO" the instance collation, I added that at the front.

DECLARE @InstanceCollation sysname

SET @InstanceCollation = CAST( DATABASEPROPERTYEX ( 'tempdb', 'Collation' ) AS sysname)

set SET @NewCollation = @InstanceCollation

Henrik Staun Poulsen SSCertifiable Points: 6578 More actions · Answer 11

Vrosenberg,

Thank you very much for those suggestions.

Best regards,

Henrik

UMG Developer SSChampion Points: 13482 More actions · Answer 12

Vrosenberg (8/7/2010)
select distinct 'ALTER TABLE .[dbo].['+rtrim(P.name) +'] CHECK CONSTRAINT all'
from [INFORMATION_SCHEMA].[CHECK_CONSTRAINTS] C
join sys.sysobjects O on name=CONSTRAINT_NAME
join sys.sysobjects P on P.id=O.parent_obj

You really should have the WITH CHECK option in that to make sure that the constraints are trusted again, otherwise they aren't as valuable to the optimizer:

SELECT DISTINCT

'ALTER TABLE [dbo].[' + RTRIM(P.name) + '] WITH CHECK CHECK CONSTRAINT all'

FROM [INFORMATION_SCHEMA].[CHECK_CONSTRAINTS] C

JOIN sys.sysobjects O

ON name = CONSTRAINT_NAME

JOIN sys.sysobjects P

ON P.id = O.parent_obj

It would be nice to see these changes integrated into the script, as well as support for multiple schemas, but it is a very good start, thanks!

nemerko Grasshopper Points: 11 More actions · Answer 13

Hello All,

I'm trying to change my database collation. I read your instructions and tried your code sample too.. But after run your script on my database, there was such as errors:

"The object 'xxxx' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation."

Some of objects listed in that error report and couldn't change collation. How can i fix it? Could you help me too? Thanks in advance..

Henrik Staun Poulsen SSCertifiable Points: 6578 More actions · Answer 14

Hi nemerko,

If you have some objects with SCHEMA BINDING, such as UDFs, index views and properly other stuff too, then you need to script those out separately.

It is, as others point out, a bit of a hassel.

But it beats exporting everything to csv files, and importing it again.

Best regards,

Henrik

Oksana March SSChasing Mays Points: 603 More actions · Answer 15

Hi,

Could you please tell me: I have a very large database with warehouse data for many years and I have a need to enter Russian characters into one column. How can I do this??? Thank you!