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 Administration (2000)
 Changing table collation in SQL server 2000

Author  Topic 

piskovic
Starting Member

7 Posts

Posted - 2006-11-20 : 10:00:04
This is the problem: collation on the server is SQL_Latin1_General_CP1_CI_AS and with that collation I can’t write Croatian symbols, meaning the server changes the symbols as follows:
È -> C
Æ -> C
Ð -> ?
Š, Ž are left unchanged. When installing the program, SQL server is installed automatically with collation settings as written above. I want to change collation settings on the database that is my program accessing, but it doesn’t work. When I write:

ALTER DATABASE xy COLLATE SQL_Croatian_CP1250_CI_AS

database collation is changed, but table collation remained the same. Then I tried

ALTER TABLE xxyy COLLATE SQL_Croatian_CP1250_CI_AS

and got the following message:

Incorrect syntax near the keyword 'collate'.

and don’t know how to get this to work… Please, help me because this is serious problem and I really need Croatian symbols. I’m newbie in SQL, so, please, write detailed explanations what I have to do.

Thank you very much and sorry for the long post.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-20 : 10:04:41
You can't set Collation at table level, you have to change collation of individual character columns using COLLATE clause.

Also note, changing collation doesn't have any effect on current data, collation changes will be applicable only on the newly entered data.

Harsh Athalye
India.
"Nothing is Impossible"
Go to Top of Page

piskovic
Starting Member

7 Posts

Posted - 2006-11-20 : 10:16:57
I tried also:

ALTER TABLE xxyy ALTER COLUMN yyzz COLLATE SQL_Croatian_CP1250_CI_AS

and got the same message. Database is currently empty and the main reason why is it empty is that collation is not OK. I am late with this project mainly because I can't get this collation right...

Thank you!
Go to Top of Page

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-20 : 10:21:12
It's bound to give error because you haven't specified data type of the column in ALTER TABLE statement.

ALTER TABLE xxyy 
ALTER COLUMN yyzz VARCHAR(100) COLLATE SQL_Croatian_CP1250_CI_AS



Harsh Athalye
India.
"Nothing is Impossible"
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2006-11-20 : 10:27:10
Watch out if your are using Temporary Tables which involve the columns you are changing collation on, or comparing these columns with others in another database/server that use different collations.

If you are doing either of those please ask and I can provide more details.

Kristen
Go to Top of Page

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2006-11-20 : 10:35:37
If the database is empty, you may be best off by dropping and recreating the table. All character based columns will pick up the database default collation at that point.
Go to Top of Page

piskovic
Starting Member

7 Posts

Posted - 2006-11-20 : 10:43:02
Thank you harsh, I don't have error with executing ALTER command, but now I have different problem. After executing:

alter table cardholder alter column firstname varchar(100) collate SQL_Croatian_CP1250_CI_AS;

I get the following error:

Server: Msg 5074, Level 16, State 8, Line 1
The index 'IX_CardHolder' is dependent on column 'firstname'.
Server: Msg 5074, Level 16, State 1, Line 1
The index 'IX_CardHolder_2' is dependent on column 'firstname'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE ALTER COLUMN firstname failed because one or more objects access this column.

How to solve that? Where are index 'IX_CardHolder' and index 'IX_CardHolder_2'? I forgot to mention that SQL 2000 server was installed with the software that is accessing the tables. Now I'm testing it to see if I can get it to work with croatian symbols. If I could do that, it would be excellent!

Thank you all very, very much!!
Go to Top of Page

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-20 : 10:59:52
The error is due to the fact that there are indexes on the column you are altering. Script all the indexes on that column, drop the indexes which depends on that column, alter the table with new collation and recreate the indexes.

Harsh Athalye
India.
"Nothing is Impossible"
Go to Top of Page

piskovic
Starting Member

7 Posts

Posted - 2006-11-21 : 02:33:03
OK, I understand what I have to do, but the problem is that I don't know how to do that... If I use only DROP INDEX IX_CardHolder and after ALTER, CREATE INDEX name, somehow I think that it won't work that easy. Do you know some web page where I can find instructions how to do that what harsh described above, preferably step-by-step? I have feeling that I’m very close to the solution of this problem, but I still don’t know how to solve it :)…

Thank you very, very much!!!
Go to Top of Page
   

- Advertisement -