Posted by Ryan on 12/16/05 17:33

I've stumbled across the above error and am a little stuck.

I've just installed SQL2000 (sp3) on my PC and restored a database from
our Live server. On a simple Update statement on a #temp table, it
fails with the above message. I think I understand what it means and
found some old posts suggesting using the following :

select name, databasepropertyex(name, 'collation')
from master..sysdatabases
select serverproperty('collation')

All of the databases that are there by default are set to
'Latin1_General_CI_AS' and the restored db is
'SQL_Latin1_General_CP1_CI_AS'.

The live server has all of these set to the 'SQL...' version, but a
standard install points to the other. So, how do I change mine to the
'newer' setting ? All I need to do is mimic the live environment for
testing and development. There is only me using it, and it's not a
problem to bin it and re-install, or tweak if I need to.

I've tried using :

ALTER DATABASE Northwind COLLATE SQL_Latin1_General_CP1_CI_AS

as a test (thought this was the best example to show), but it fails
stating the following :

Server: Msg 5075, Level 16, State 1, Line 5
The object 'CK_Products_UnitPrice' is dependent on database collation.
Server: Msg 5075, Level 16, State 1, Line 5
The object 'CK_ReorderLevel' is dependent on database collation.
Server: Msg 5075, Level 16, State 1, Line 5
The object 'CK_UnitsInStock' is dependent on database collation.
Server: Msg 5075, Level 16, State 1, Line 5
The object 'CK_UnitsOnOrder' is dependent on database collation.
Server: Msg 5075, Level 16, State 1, Line 5
The object 'CK_Discount' is dependent on database collation.
Server: Msg 5075, Level 16, State 1, Line 5
The object 'CK_Quantity' is dependent on database collation.
Server: Msg 5075, Level 16, State 1, Line 5
The object 'CK_UnitPrice' is dependent on database collation.
Server: Msg 5075, Level 16, State 1, Line 5
The object 'CK_Birthdate' is dependent on database collation.
Server: Msg 5072, Level 16, State 1, Line 5
ALTER DATABASE failed. The default collation of database 'Northwind'
cannot be set to SQL_Latin1_General_CP1_CI_AS.

On the Live server, the Northwind database is set to the 'SQL...'
version, so it MUST be do-able somehow.

Any pointers would be appreciated.

Thanks in advance


Ryan

[Back to original message]