Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
change colate for DB in sql 2000
- Thread starter fidel2k
- Start date
tlbroadbent
MIS
See "ALTER DATABASE" and "Changing Collations" in SQL BOL.
ALTER DATABASE dbname COLLATE <New collation> If you want to get the best answer for your question read faq183-874 and thread183-468158.
Terry L. Broadbent - DBA
SQL Server Page:
ALTER DATABASE dbname COLLATE <New collation> If you want to get the best answer for your question read faq183-874 and thread183-468158.
Terry L. Broadbent - DBA
SQL Server Page:
Hi!
Well, this works fine. But changing the collation to <any kind>_CS_AS will make the strings case sensitive (this is ok) but the tables names, too!
Is there any way to change ONLY the case sensitiveness of the strings (the columns) - for the whole db?
(you can change it for each column for each table but is this the only way?)
Well, this works fine. But changing the collation to <any kind>_CS_AS will make the strings case sensitive (this is ok) but the tables names, too!
Is there any way to change ONLY the case sensitiveness of the strings (the columns) - for the whole db?
(you can change it for each column for each table but is this the only way?)
Hi
The problem(or advantage) of SQL 2000(you didn't specify which version you are using) is that your server can use one collation, you database a different collation and in your tables you can have multiple collations at a column level.
The ALTER DATABASE statement will change the entire database collation but won't change the columns. To do that you actually have to change it for each column. Here is a script I wrote a few weeks back to do a global column collation update.
Basically it uses the system tables to retrieve the table name, column name, length of the column, and only coulms whose datatype uses a collation and I have hardcoded the collation I want to change to. I also specified the current collation so that only the incorrect columns are updated.
Run the script in Query Analyser and select "Results in Text", once the script has executed you will see a list of ALTER TABLE, ALTER COLUMN commands just paste this into another query window and execute it against your database.
Make sure you know which collation you want to use first and what the existing one is.
I still think you should look into changing the database collation as well. The reason being that all stored procedure variables that use character data are saved in the syscolumns table and use the database collation and if you try a join with a variable using the db collation and a field using your new collation you might end up with a "cannot resolve collation error" because the collations are different.
Secondly when creating tables or adding columns if you don't specify the "new" collation the database collation is used by default which means you could run into more errors.
If that happens you might have to go through the whole process again or use the COLLATE function on every join that uses columns with character datatypes in every query.
Also I have tested this in SQL 2000, SQL 7 has slightly different tables so it might not work in SQL 7. Make sure you test this first in a dev instance before you apply this to your production database.
here is the sql script:
select 'ALTER TABLE ' + so.name + char(13) + 'ALTER COLUMN ' + ' ['+sc.name+'] '+ '['+st.name+']'+ '('+convert(varchar(5),sc.length)+')'+ 'COLLATE SQL_Latin1_General_CP1_CI_AS NULL'+ char(13)+ 'GO'
FROM sysobjects so, syscolumns sc, systypes st
where so.id = sc.id
and sc.xtype = st.xtype
and st.xtype in(175, 239, 99, 231, 35, 167)
and sc.collation = 'Latin1_General_CI_AS'
and so.type = 'U'
Hope this helps
John
The problem(or advantage) of SQL 2000(you didn't specify which version you are using) is that your server can use one collation, you database a different collation and in your tables you can have multiple collations at a column level.
The ALTER DATABASE statement will change the entire database collation but won't change the columns. To do that you actually have to change it for each column. Here is a script I wrote a few weeks back to do a global column collation update.
Basically it uses the system tables to retrieve the table name, column name, length of the column, and only coulms whose datatype uses a collation and I have hardcoded the collation I want to change to. I also specified the current collation so that only the incorrect columns are updated.
Run the script in Query Analyser and select "Results in Text", once the script has executed you will see a list of ALTER TABLE, ALTER COLUMN commands just paste this into another query window and execute it against your database.
Make sure you know which collation you want to use first and what the existing one is.
I still think you should look into changing the database collation as well. The reason being that all stored procedure variables that use character data are saved in the syscolumns table and use the database collation and if you try a join with a variable using the db collation and a field using your new collation you might end up with a "cannot resolve collation error" because the collations are different.
Secondly when creating tables or adding columns if you don't specify the "new" collation the database collation is used by default which means you could run into more errors.
If that happens you might have to go through the whole process again or use the COLLATE function on every join that uses columns with character datatypes in every query.
Also I have tested this in SQL 2000, SQL 7 has slightly different tables so it might not work in SQL 7. Make sure you test this first in a dev instance before you apply this to your production database.
here is the sql script:
select 'ALTER TABLE ' + so.name + char(13) + 'ALTER COLUMN ' + ' ['+sc.name+'] '+ '['+st.name+']'+ '('+convert(varchar(5),sc.length)+')'+ 'COLLATE SQL_Latin1_General_CP1_CI_AS NULL'+ char(13)+ 'GO'
FROM sysobjects so, syscolumns sc, systypes st
where so.id = sc.id
and sc.xtype = st.xtype
and st.xtype in(175, 239, 99, 231, 35, 167)
and sc.collation = 'Latin1_General_CI_AS'
and so.type = 'U'
Hope this helps
John
Hi!
(SQL Server 2000 or MSDE 2000)
Thanx for the hint. This is the way I looked for a workaround for. So it seems to me a MS "feature"
(why on earth they make the table names case sensitive? So a "select ... from TEST" not equals "select ... from test"
But if there´s no other way we have to change all (relevant) columns
(SQL Server 2000 or MSDE 2000)
Thanx for the hint. This is the way I looked for a workaround for. So it seems to me a MS "feature"
(why on earth they make the table names case sensitive? So a "select ... from TEST" not equals "select ... from test"
But if there´s no other way we have to change all (relevant) columns
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question
