SQL Server Performance Forum – Threads Archive
Syntax error when changing db collation.
Hi there. I’ve done about 10 USE dbgo
ALTER DATABASE db
COLLATE Latin1_General_CI_AS in a row (migrating stuff from a old sql 7 server to an SQL2000 instance on a new Consolidated environment). All of the previous alters went fine, now this one is giving me a syntax error: USE psgt
go
ALTER DATABASE psgt
COLLATE Latin1_General_CI_AS Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near ‘Latin1_General_CI_AS’. I’ve tried brackets in all different place, etc. And, all the db’s prior to this one, comes from the same source server, and is placed on the exact same destination. Any thoughts guys?
You need to check for several things (this is taken from Books Online) If the following objects, which are dependent on the database collation, exist in the database, the ALTER DATABASE database COLLATE statement will fail. SQL Server will return an error message for each object blocking the ALTER action: – User-defined functions and views created with SCHEMABINDING.
– Computed columns.
– CHECK constraints. Table-valued functions that return tables with character columns with collations inherited from the default database collation. It sounds like if there are any of these, it should return a specific message about them being there, but it’s the only thing I can think of straight away.
Tom Pullen
DBA, Oxfam GB
Hi all. For interest sake…. my problem is solved. The spesific db that gave the syntax error was still on Compatibility Level 65, and not on 80, like the other db’s. Ciao.
]]>