We have a SQL 2014 instance that is dedicated to one application database. While upgrading the application we learned there were several collation conflicts, causing the upgrade to fail.
SQL instance: SQL_Latin1_General_CP1_CI_
Application database: Latin1_General_CI_AS
- 800 columns: SQL_Latin1_General_CP1_CS_AS
- 200 columns: Latin1_General_CI_AS
We took the path of least resistance and corrected collation on the SQL instance, database, and 200 columns to match the collation of the 800 columns in our database - everything is now SQL_Latin1_General_CP1_CS_
AS. Performance is noticeably better and the application upgrade succeeded.
However, now when running SQL statements against the database, objects must be referenced with case sensitivity. I knew this would happen because we're using a case-sensitive collation, but I didn't realize the inconvenience.
None of my reports are working unless I convert all tables/columns in my query to the proper case. We have a lot of reports that are completely lower-case and they no longer run.
Given our current scenario, is there anything we can do so SQL accepts our lower-case statements? Is it because we changed collation of the SQL instance or was it collation of the database that caused this behavior?
Thanks for your expertise!