Collation can be set on multiple levels on a server though is principally at the server level and at the column level on tables. If these collations are different, temporary tables created on the Master database will not be able to resolve with those on the database columns and queries will throw an error.
Recently I experienced a problem where the Live database and server used one collation type and the Development environment used another. When it came to running scripts on these two environments we couldn’t be sure that the same script would have the same result on both servers.
One solution for resolving these collation problems is explicit declaration of collation within the database.
To do this:
- Script all tables of the existing database, without Indexes, Constraints, Primary Keys, etc.
- Update the generated script such that all collations match the preferred collation.
- Create a new database, using the preferred collation.
- Run the generated scripts against that database.
- DTS the data from one to the other, without using the ‘create objects’ option, and with ‘use collation’ checked.
- Script the entire original database, with primary keys, stored procedures, etc but WITHOUT the ‘Include DROP commands’
- Update the script to use the preferred collation.
- Run the script against the new database. The table creation lines will fail, but everything else should run, leading to a database with the data in the correct collation.
Tags: Collation, Database, SQL, SQL Server, SQL Server 2000