How I was Completely Outwitted By SQL Today

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.

2014-03-24 15_15_58

(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.

Author: Richie Lee

Full time computer guy, part time runner. Full time Dad, part time blogger. Knows a thing or two about Pokémon. Knows too much about SQL Agent. Writer of fractured sentences. Maker of the best damn macaroni cheese you've ever tasted.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s