Search Knowledge Base
KB #240034: Error – Cannot resolve collation conflict for concatenation operation
When encrypting or decrypting a column when using a non-Western version of SQL Server, you may receive an error similar to the following:
Server: Msg 446, Level 16 Cannot resolve collation conflict for concatenation operation.
Important: this can potentially lead to corruption of the data in the column
Encryptionizer installs the UDFs in Master. If the Collation Value of the target database does not match the Collation Value of Master, SQL Server may become confused when trying to encrypt or decrypt a column or variable using one of the fn_n_encrypt_ or fn_n_decrypt_ functions.The solution is to install the UDFs in the database which contains the columns being encrypted. In fact, we recommend this solution even if the collation values do match.
To install and use the UDFs in the target database, modify ADD_UDF_XP.SQL and comment out the following line:
Then use Query Analyzer to run ADD_UDF_XP.SQL in the desired database.Finally, modify any function calls to refer to the current database, rather than Master. For example, change:
update customer set CreditCard = master.dbo.fn_n_encrypt_char(@cc,0,0,1)
– to –
update customer set CreditCard = dbo.fn_n_encrypt_char(@cc,0,0,1)
Tip:The Collation Name can be found in Enterprise Manager under the Database Properties (right click on database name).
The Collation Name can be found in Query Analyzer by querying the ‘collation’ property with DatabasePropertyEx. E.g.,
240028: How to Install APIs Manually