Over the weekend a SQL Server went live in one of our environments. There’s a permission script I needed to run this morning. This script I’ve used a fair few times on different servers, and not had a problem with it. However, today it failed with the following error message.

“Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict between “Latin1_General_CI_AS” and “SQL_Latin1_General_CP1_CI_AS” in add operator.”

Eh? Time to refer to the documentation team. Apparently the issues surrounded different collation styles. But all our databases run ‘Latin1_General_CI_AS’. Plus this script is a simple permission script. then I read that it could be conflicting collation in TempDB. So I checked the collation for TempDB and it is exactly the same. At any rate, that did not make sense because I doubt this SQL script required enough resource to require TempDB usage!

At this point I decided to check the collation of all the databases. I had check the collation for tempDb via sys.databases, so checked for all of them.

(Most of you are probably already ahead of me on this one…)

What’s this? SSISDB’s collation is different. But I’m not using SSISDB! What I was doing of course was trying to print out some SQLto execute on another window, but because my current available database was SSISDB, I was unable to print out the SQL. So I changed to any other database and the script ran fine no issues.