SQL Collation Conflict When Installing EIOBoard
Posted by Casey Crookston on 18 January 2018 04:50 PM
When installing EIOBoard, it's possible that you may run into a SQL error similar to this one:
The error itself will only be found in the install logs. It will most likely manifest itself with a failed install that will auto roll-back.
Understanding Database Collation:
Here's a good article that explains it in simple terms:
Cause of the problem:
When EIOBoard is installed, the SQL script that creates and populates the database assumes that the database will be set to the collation of "SQL_Latin1_General_CP1_CI_AS". In many places (ie: hundreds) this value is hard coded in the scripts where it sets columns to use this collation. The problems stems from the database itself being set to another collation. SQL won't let tables and columns have a collation that is different from the database itself.
We have run into cases in the past where customers have an existing SQL Server install where the server is set to a different collation. When an EIOBoard database is created, it assumes the collation of the server, so when the script is run, it fails.
For now, the best work-around is to have EIOBoard install it's own, stand-alone instance of SQL Server Express.