Restoring SQL 7 backups onto SQL 2000

[jschaddock]

Hello,

I'm upgrading some databases from SQL 7 to SQL 2000. Basically the first thing I have done is to restore a backup from 7 into 2000 which seems to have run ok. However, I am getting a problem when trying to run some of the stored procedure relating to collation of tables.

What seems to be the problem is that some of the columns are set to a collation level that SQL 2000 does not allow or recognise. I have tried using the ALTER TABLE command to change the collation level on the columns but it does not seem to work, the lowest down I can get is that I can change the default collation level of the table but this does not help me to change existing columns.

I have gone in to enterprise manager and changed the level manually then saved the script that SQL produces for this change. However, this script basically involves creating new tables with the new property and then importing the data in (effectively what DTS does) and dropping the old table. I am trying to avoid this course of action if possible due to the large amounts of data involved so basically my question is:

Does anyone know any ways of changing collation levels at column level on currently existing table columns?

Thanks

J

 

[SonOfEmidec1100]

You can use ALTER TABLE - BOL does not make it obvious that you need to alter the data type as well, even if you alter it to the same value.

ALTER TABLE Table1 ALTER COLUMN myname VARCHAR (50) COLLATE SQL_Latin1_General_CP1253_CI_AS

 

[jschaddock]

Thanks for that, I ran the code and it worked fine.
Yes, BOL doesn't make it clear that you have to enter datatype so I hadn't tried that.

Thanks

J