Change collation of fields in SQL database

[GregC]

I have a database that was set up as case sensitive and needs to be changed to case insensitive. The original collation for the db was SQL_Latin1_General_CP1_CS_AS and I need to change it to SQL_Latin1_General_CP1_CI_AS. I already did an (alter database <dbname> collate SQL_Latin1_General_CP1_CI_AS) to change the database default but now I need to change the collations in the text and varchar fields in all the tables (approx 350 tables) to the same. Is there a simple script or statement that can be run to do this? There are multiple databases on this server and only my db needs to be changed. The others cannot be changed.

 

[aggle-rithm]

I have a database that was set up as case sensitive and needs to be changed to case insensitive. The original collation for the db was SQL_Latin1_General_CP1_CS_AS and I need to change it to SQL_Latin1_General_CP1_CI_AS. I already did an (alter database <dbname> collate SQL_Latin1_General_CP1_CI_AS) to change the database default but now I need to change the collations in the text and varchar fields in all the tables (approx 350 tables) to the same. Is there a simple script or statement that can be run to do this? There are multiple databases on this server and only my db needs to be changed. The others cannot be changed.

Which version of SQL?

Is it Microsoft or Sybase?

 

[GregC]

Microsoft SQL 2000.

 

[aggle-rithm]

Microsoft SQL 2000.

Have you looked at your syscolumns table for that database to verify that you need to do anything?

I experimented with this, and found that when I altered the database, it set all the char/text fields to the new collation automatically. Be default, these fields use the database default collation.

If it didn't do that, you would have to enable modification of system tables, then change the collationid field in the syscolumns table (not the collation field itself, since that is calculated from the collationid field), for each column with character values.

 

[aggle-rithm]

FYI, collation ID's:

SQL_Latin1_General_CP1_CS_AS -- 855687176

SQL_Latin1_General_CP1_CI_AS -- 872468488

I can't guarantee they would be the same on your system, but they probably are.

 

[GregC]

Thanks, I'll look into it.

 

[GregC]

a-r.

Thanks for the help.

What I'm going to have to do to solve my problem is to create a new instance of SQL, make sure the default collation is set to CI and move my data over.

My problem is that I'm sharing the sql server with our financial software. The software is CS and the server was set up with them in mind. When I moved my GIS data to SQL the IT director wanted to keep it simple and have only one sql running. I was running ArcSDE 9.1 to connect to my data and it alowed for CS databases. I upgraded to v9.2 and that's when all my problems began. It seems the 'bug' that allowed my CS database to run in 9.1 was 'fixed' in 9.2. Even changing the collation on my db is not enough. 9.2 won't even run on a server where the default collation is CS. It's funny, the dev team didn't find the 'bug' in v9.1 until I sent them my DB.

Once again, thanks. You're help was appreciated.