How to clear the collation of a column
-
hello together,
in some columns of our database is a specific collation active. The collation of the affected columns is the same as the database default collation. We never modified the collation of a column explicit, but perhaps it has been done by a transfer of the database or by restoring it on a new SQL Server installation.
Now the problem:
if we transfer the database to another database with different database collation, some "create view" statements will not work.
Because we dont need a column defined collation, my idea was to clear the column based collation, but with "alter column ... collate database_default/null" or similar expressions this does not work.
My prefered way to do the job is to clear the collation field in syscolumns. But I dont know if there are side effects.
Can anyone give me an idea how to clear the column defind collations?
Thanks, Klaus
-
It's not recommended to tamper with the systemtables manually (as I'm sure you know) Even if it would work, support is void if you go this route.
I haven't had the 'opportunity' to dig into this issue, but what comes to mind is to drop the columns and then recreate without any explicit collation clause. Not the most 'elegant' way, though.
/Kenneth
-
Thanks you for your answer.
If I say 'some' columns I mean over 2000 colunms. So it's not a good idea to alter them per drop an create.
Klaus
-
how about :
alter table mytable
alter column mycol type_and_length COLLATE mynewcollation
Ofcourse backup before!!
Maybe even script the full db, create a newone, run the ddl, and import all data if you can.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Hello,
this does not clear the column collation. If, after column altering, the database collation is changed it takes no effect on the altered columns.
I want to get rid of the column collations.
Thanks, Klaus
-
Use the statement below to identify the undesirable collations for all columns in a db.
SELECT TABLE_NAME, COLUMN_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE NOT COLLATION_NAME IS NULL
AND COLLATION_NAME <> 'insert your desired collation here to display non-conforming collations'
ORDER BY TABLE_NAME, COLUMN_NAME
Insert the recordset into a temp table.
Next step, alter the columns by using the ALTER TABLE statement with a generically created string in a cursor using sp_executesql. Search for "changing collations" in BOL.
Max
-
I had this problem as well and I think it was somewhere on the Microsoft site I found this advice. 1) Script the database and turn on "Script only version 7 compatible options"
2) Create a new blank database and run the script to recreate the schema
3) export/import the data from the old db to the new
4) rename databases to switch the good one (without collation) into the place of the old bad one.
I have tried this and it worked quite well although the database I was repopulating didn't have any foreign key constraints to complicate the data transfer sequence......
Hope it helps..
Allan
-
If for any reason your database collation is changed, your existing tables will not be affected. If they used the "old" db-collation, they will still use the "old" db-collation, even after the alter-db to the new collation !
The db-collation is not more than a default collation for columns which haven't got one specified at creation time.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
in lack of better suggestions i'll prefere Allan's plan, because I excpect to loose the column collation this way (what's not in the script can not be written to the new db).
Thanks to all, Klaus
-
That works fine if you don't use any sql2k features or new datatypes (e.g. tinyint, UDF,...)
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Umh, must be the clown in me, but what collation would you suggest on a TINYINT column?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
early in the morning
it just scripts the non sql7 stuff and haves you run into the problem when you run the script on a sql7 system
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Haha, I know you don't take it too serious!
Here's one smilie of the fun section
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply