For some strange reason, our software requires SQL Server to run with a specific collation. Basically the collation specifies the "order" in which text data is stored and has an impact on how indexes manage and search through large amounts of data.
Our issue comes up when the DATABASE collation doesn't match the SERVER collation. We'll get something like this...
SqlException cannot resolve the collation conflict between 'Latin1_General_CP1_CI_AS' and 'SQL_Latin1_General_CP1_CI_AS'.
All I can tell you from this exception is that the two collations don't match and our software won't run. Essentially we have to re-install sql server to reset the collation on the server so that it matches the database.
You'd think this would be a pretty easy configuration option during the sql server install... not so. During the sql install you do get a prompt to choose the server collation but the list is language based... nothing mentioning SQL_Lation1_SomethingOrRather.
There is an option on there for legacy collations, since our app was developed on an older version of SQL Server this would make sense, but again nothing mentioning SQL_Latin1.
Well, turns out that the option for "Dictionary order, case-insensitive, for use with 1252 Character set" is actually equivelant to our SQL_Latin1 collation and once the database server comes up sure enough it's been configured properly.
Tricky config setting but at least now we'll know what to look for on new installs. I'd still love to know WHY we need to run with that specific setting...