1

I have a column that was previously protected with an Always Encrypted certificate. It was determined that this column didn't need the encryption and we wanted to do some analysis on it so I went back and decrypted the column. However, when I try to select the column in a stored procedure I get the following 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_BIN2" and "SQL_Latin1_General_CP1_CI_AS" in CASE operator."

I've only gotten this message in the past when selecting from a column that is encrypted so I'm unsure why I'm still getting it for this now decrypted column. When I right click and script out the table schema it no longer shows any encryption or collate settings for that column. I've also run the database Always Encrypted wizard again and it shows that it's not protecting that column anymore either. So I'm confused why the database thinks the column is still encrypted.

Any suggestions would be greatly appreciated. Thank you.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
adam
  • 502
  • 1
  • 6
  • 17
  • 2
    By default, scripts do not include collations (to do that, you need a Generate Script task with custom options), so most probably the column *is* still using a non-standard collation (likely `Latin1_General_BIN2`, as that makes sense for encryption). You can verify this by checking the column properties in SSMS. Changing a column's collation is not a cheap operation (it effectively rewrites the entire table), but fortunately it's not a difficult one. – Jeroen Mostert Apr 24 '19 at 13:22
  • That fixed my problem. For anyone who comes in the future you can see the collation of the column by right clicking the column and checking the properties. To update the collation run a script like "alter table tableName alter column columnName columnType collate SQL_Latin1_General_CP1_CI_AS null" – adam May 15 '19 at 17:25

0 Answers0