How to make VSTS DBPro work with the default database collation

*Moved to: http://fluentbytes.com/how-to-make-vsts-dbpro-work-with-the-default-database-collation/

today, I had a few hours of frustration, getting all databases in our project use the same collation.

What I wanted to achieve is that we would use the default collation of Latin1_General_CI_AS but not script that in our SQL scripts.

To start, for some reason the DBPro team decided not to use the names we are used to when we are working with collations. The list we all know (and love ahum ..) doe not show any relationship with the names used for the model collation. So first you need to figure out that English(United States) (1033) – CS, means Latin1_General_CS_AS .

As you might know the default collation for SQL 2008 default installation on an US English machine is Latin1_General_CI_AS, so that does not match by default. So I started to change this setting in all our database projects.

Then I wanted to set the scripts not to contain the collation in the scripts. To do this you can go to the  deploy Tab and then click the Deployment Configuration file Edit button.

There you will get the following settings page with the dropdown where you can select the “deployment collation default” for your DBPro project.

Now we get to the fun part, because when you select the option “Do not script the collation” and you hit the deploy button I constantly got the message:

Warning TSD00258: The project and target databases have different collation settings. Deployment errors might occur.

Now that is interesting, I know my database is the default collation, I set the project to use English(United States) (1033), so why do I get this message?!?

So after some searching I found yet another location where you can set the collation and that is in the Catalog properties file on the first properties page. So I selected the option edit and got the following page:

Ok so I see, the collation is here CS and a SQL collation, so I changed that one also to Latin1_General_CI_AS.

So now I should be good right?

Well no, no luck. It just seems there is no way to get rid of the message telling me I have a different collation then the target database.

At this moment I am just stumped and do some additional inquiries why this is the case. The only way for now to make it work for me without warnings during build, is to set the “deployment collation default” back to “Use the collation of my project”.

It seems like a bug at this moment that I get this warning, but once I know for sure, i will post the update here.

Cheers,

Marcel

Follow my new blog on http://fluentbytes.com