alterdatase to change sort order 1

[Luvsql]

I've run the following to change our database to case insensitive (installed instance of sql 2000, then attached db into it):

ALTER DATABASE X
COLLATE SQL_Latin1_General_CP1_CI_AS

It fails on all the Check Constraints objects (over 2100 of them). I've also read that I cannot alter table to change the sort order if a table has a constraint on it.

Is there anyway to drop the constraints first, then run alter database, then recreate just the constraints? It looks like I'll have to drop each table, recreate in case-insentive, then DTS or bcp in. Yuck.

Has anyone successfully done this?

 

[StoneDeCroze]

alter the collation on the tables 1st:

ALTER TABLE [TableName] ALTER COLUMN [ColumnName] [varchar] (50) COLLATE Latin1_General_CI_AS

Here's a script to generate all the alter statements:
SELECT
'ALTER TABLE ['+TABLE_NAME+'] ALTER COLUMN ['+COLUMN_NAME+'] ['+DATA_TYPE+'] ('+RTRIM(CHARACTER_MAXIMUM_LENGTH)+') COLLATE Latin1_General_CI_AS'
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
COLLATION_NAME != 'Latin1_General_CI_AS'

 

[Luvsql]

I've pasted this into our old server, saved the results, then ran it on our new server's databases (which were attached from mdf's of old therefore still binary) and I get the following errors:

Server: Msg 131, Level 15, State 2, Line 54
The size (2147483647) given to the column 'ItemText' exceeds the maximum allowed for any data type (8000).

This repeats for quite a few more tables. These are text and image type data types.

 

[Luvsql]

I removed those tables which gave this error (just to see if it would work) and now I receive an error on the very first one:

Server: Msg 5074, Level 16, State 8, Line 1
The object 'PKSY04000' is dependent on column 'BACHNUMB'.
Server: Msg 5074, Level 16, State 1, Line 1
The object 'PKSY04000' is dependent on column 'BACHNUMB'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE ALTER COLUMN BACHNUMB failed because one or more objects access this column.

 

[StoneDeCroze]

doh,

sorry - mixed up myself there.

Attach the SQL7 DB's to the 2000 server.
Change the default DB collation (sp_dboption)
then run the script to get the ALTER statements and then run the ALTER statements.

It's 99% good - where it falls over is if you have a relationship between two tables based on text fields. You will have to break these 1st, run the ALTERS and then re-create the relationships.

You cannot change the COLLATION on anything less than an entire SERVER prior to SQL2000 - my error.

Peter

 

[Luvsql]

These are sql 2000 databases and not 7?

Does this mean it is not possible to change collation on any table that has a text field that's a primary/foreign key? This fied that it failed on (BACHNUMB) is a char field, not text.