How are the different collation settings related and applied in the SQL Server project type in Visual Studio 2010?

1. In开发者_运维问答 the Project Settings tab of the project properties page there is an option called Collation for database model.

2. There is also an option in the project settings (the .sqlsettings file) called Database collation.

3. And then in the Deploy tab of the project properties page there is a link to the Deployment configuration file which has an option called Deployment comparison collation.

I'm confused, but more importantly, even though I have set that last option to Use the collation of the server the deployment script always contains the following statement:

ALTER DATABASE [$(DatabaseName)] COLLATE Latin1_General_CI_AS;

Which results in the following error:

ALTER DATABASE failed. The default collation of database 'Database' cannot be set to Latin1_General_CI_AS.

Ideally I don't want to think about collation, and always follow what is set at the target database level, but somehow the various options of the SQL Server project make it hard to predict what's going to happen at actual deployment.

Can you explain what each of these options do and how they interact with and/or override each other?

---

While I cannot shed much light on what the plethora of different collation settings do, I can point out one setting that helped me when searching for a remedy to the ALTER DATABASE COLLATE always being in the deployment script.

In the Database Project => Properties => Database.sqldeployment settings there is a ScriptDatabaseCollation that when unticked, fixed my issue.

---

I believe the COLLATE Latin1_General_CI_AS that you are seeing is coming from the Default Collation specified in your Project Setting. This can be accessed via:

• Project Properties > Project Settings > Edit the Catalog properties file

This will open the Database.sqlsettings and you will see the first entry is the Default database collation that your project uses when generating scripts.

The Deployment Comparison Collation is used for the database comparing models when you deploy the project. I think this only functions if you are deploying directly to a database and not if you are using the default settings of generating a .sql script file.

As eddie noted, there is a setting in the Database.sqldeployment file that when unchecked, removes the annoying collation specifier from the CREATE and ALTER scripts.