Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2005
    Posts
    1

    Unanswered: Updating systemtables directly to change column-collations

    Hi there.

    Is there any way to update the system tables directly, to alter the collations of the columns in the user db's?

    I've tried the script below:

    UPDATE Syscolumns SET collation = 'SQL_Latin1_General_CP1_CS_AS' WHERE name = '<AddrCode>'
    AND id = object_id('<Compliance>')

    but, I get the following error message:

    Server: Msg 271, Level 16, State 1, Line 1
    Column 'collation' cannot be modified because it is a computed column.

    Can you please help me! I need to do thousands of these, and most of them has constraints on, so my script I generated to do the ALTER TABLE.... ALTER COLUMN does not suffice.

    Thanks.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    DANGER WIL ROBBINSON


    If I had to do this I think I'd bcp out all the data, script the database and rebuild it from scratch
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Not that I can think of. Consider one of those columns has an index. If you update syscolumns with a new collation (say by going from case sensitive to case insensitive), that index is now "corrupt". And that does not even get into the idea of unique indexes, which could become non-unique after such a change.

    As painfull as it sounds, you will have to go through and do all of the thousands of columns, and rest their collations with some sort of script.

    If I might ask, how did this requirement come about?


    Aw, man. I got sniped by a banner ad ;-)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •