December 23, 2003 at 3:10 am
Hi!
I have a server (MS SQL2000 sp3) with Cyrillic_General_CS_AS collation.
Some databases on the server have SQL_Latin1_General_CP1251_CS_AS collation.
Some have Cyrilic_General_CS_AS collation.
All data in these databases are replicated from other servers with SQL_Latin1_General_CP1251_CS_AS collation.
I need to make all databases have SQL_Latin1_General_CP1251_CS_AS collation.
(existing tables and columns too)
What is the best/fastest way to do this?
Thanks.
December 23, 2003 at 7:34 am
I don't know the best/fastest way but the safest way for your data is export/import.
You have to rebuild your server too since server was built with "Cyrillic_General_CS_AS collation" and the tempdb has that collation. You could encounter inaccurate result if you perform some kind of sorting and comparison by using tempdb.
December 23, 2003 at 8:15 am
ALL data in theses databases is replicated (via transactional replication) from other servers, so I guess export/import has no use.
December 23, 2003 at 9:05 am
the databases must have the same collation between the publisher and the subscriber.
Change the collarion with :
ALTER DATABASE DBName
SET COLLATE SQL_Latin1_General_CP1251_CS_AS
December 23, 2003 at 9:12 am
quote:
the databases must have the same collation between the publisher and the subscriber.Change the collarion with :
ALTER DATABASE DBName
SET COLLATE SQL_Latin1_General_CP1251_CS_AS
This will change only new columns collation...
December 23, 2003 at 10:20 pm
That's right. ALTER DATABASE will only change the data in new columns. If you don't want to rebuild your server the only way to do it would be to use ALTER DATABASE like Racosta suggested, then ALTER COLUMN on every column in your existing tables. Very time consuming in my opinion. I would be going with rebuilding the SQL Server. Then at least you will know it will be right for any new databases that get built. Also, as Allen_Cui said, if you are using tempdb for anything ie, temporary tables, it will also use the server collation. There is nothing you can do about that unless you specificallty declare the collation you want when you create the temp table.
I have had this problem a lot where I currently work and every time I find it better to reinstall SQL.
Good luck.
Angela
December 24, 2003 at 1:48 pm
You may also use rebuildm tool provided by Microsoft. Look in BOL on how to use it. This will help take care of the server and the tempdb but you will still have to alter the dbs separately.
I hope this helps in some way.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply