I know there are several threads that discuss SQL Collation Conflict but I believe this is a bit different.
I have a SHARED SQL Server (2016) set up per Microsoft's recommendation of "Latin1_General_CI_AS" instead of the legacy "SQL_Latin1_General_CP1_CI_AS". About half the database on the server are running the newer Collation and the other half the Legacy. Everything has been working fine for a long time!
However, now I am trying to host another 3rd Party's database and for whatever reason, it refuses to work. Their Software keeps crashing and complains about not being able to translate Collations (or something along those lines).
From my understanding, individual databases can specify their own Collation (obviously since we are about 50/50 now already), however any sort of Temp tables and such that a software solution creates, which I'm assuming includes queries, will be done under the Server's Default Collation.
I tried changing this new databases Collation over to match the Server but their software still threw the same error. So right now, the Vendor's suggestion is to change the Default of the Server back to the older Collation. I'm cannot risk breaking all of the existing solutions that have been running fine for years, to accommodate this one product.
So finally, my question:
Is there some backdoor trick that would force this Database and all its access to use "SQL_Latin1_General_CP1_CI_AS" instead, even while the SQL Instance's default remains "Latin1_General_CI_AS"?