SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Changing table collation in SQL server 2000
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

piskovic
Starting Member

7 Posts

Posted - 11/20/2006 :  10:00:04  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
5581 Posts

Posted - 11/20/2006 :  10:04:41  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
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"

Edited by - harsh_athalye on 11/20/2006 10:07:17
Go to Top of Page

piskovic
Starting Member

7 Posts

Posted - 11/20/2006 :  10:16:57  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
5581 Posts

Posted - 11/20/2006 :  10:21:12  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
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

United Kingdom
22415 Posts

Posted - 11/20/2006 :  10:27:10  Show Profile  Reply with Quote
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 - 11/20/2006 :  10:35:37  Show Profile  Reply with Quote
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 - 11/20/2006 :  10:43:02  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
5581 Posts

Posted - 11/20/2006 :  10:59:52  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
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 - 11/21/2006 :  02:33:03  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000