Collation Problems - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Collation Problems

Author

Topic

ChetShah
Starting Member

37 Posts

Posted - 2005-08-02 : 10:18:46

Hi,

It seems the we have discovered the server collation between 2 SQL Server 2000 are different - one is SQL_LATIN1_General_CP1_C1_AS (which i think is the default colation) and other is LATIN1_GeneralC1_AS.

Due to this we have had a number of issues and would to synchronise them as default collation.

In Books online, they mention rebuilding master using rebuild utility and also rebuiding the user database. How would it be possible to rebuild user database??

In worst case scenarion if we have to rebuild the server how will i be able to restore user database as it will have wrong collation settings??

Chet

Kristen
Test

22859 Posts

Posted - 2005-08-02 : 10:25:27

I expect one of yours was upgraded from SQL7, the other was a fresh SQL2K install.

I have once done an in-place change of collation (rebuild master etc.)

I would never do it again - I would reinstall :-(

Are you sure you need to do this?

We have a product that has to work on servers with different collation. The only things we do are:

Whenever we create a char/varchar/text column in a table we explicitly state the COLLATE. (Including columns in temporary tables in SProc etc.). My opinion on this is that it is Good Practice anyway.

If we compare (JOIN / WHERE etc.) columns from MASTER tables we explictly cast it, e.g.
SELECT *
FROM master.dbo.sysdatabases D
JOIN MyDatabase.dbo.MyTable T
ON T.name = D.name COLLATE LATIN1_GeneralC1_AS

We have close to ZERO of these, I expect you do too!

Kristen

ChetShah
Starting Member

37 Posts

Posted - 2005-08-02 : 10:41:51

Hi,

Both are actually SQL 2k machines - just that when one was built some one - (NO NAME CALLING) decided to change collation.

It seems Kristen is advising a reinstall. If we backup the database (does this use current collation settings) and if so will this not error when restoring to different collations??

Chet

Kristen
Test

22859 Posts

Posted - 2005-08-02 : 10:45:05

No error when restoring, but the data will still be in the original collation (I'm 99% sure).

So you'd have to export all the data and reimport, or create a new database and

INSERT INTO TargetDatabase.dbo.MyTable
SELECT *
FROM SourceDatabase.dbo.MyTable

(The SELECT * would need to specify all columns, with collation casting on all the "text" ones [again, not 100% sure if you need that bit]

Kristen

Kristen
Test

22859 Posts

Posted - 2005-08-02 : 10:47:39

Too hard a job to put COLLATE casting on CREATE TABLE stuff, and comparison operators between databases?

Thinking about it you can change the collation in-situ by

CREATE TABLE MyTempTable
(
MyColA varchar(10) COLLATE SQL_LATIN1_General_CP1_C1_AS
...
)

INSERT INTO MyTempTable
SELECT * -- Maybe needs COLLATE casting
FROM MyRealTable

DROP TABLE MyRealTable

sp_rename 'MyTempTable', 'MyRealTable'

It will be a bit harder if you ahve Foreign Keys and stuff

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources