KB #240034: Error - Cannot resolve collation conflict for concatenation operation

Search Knowledge Base

KB #240034: Error – Cannot resolve collation conflict for concatenation operation

Type:

Fix

Summary:

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

Additional Information:

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:

   USE MASTER

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

   select DatabasePropertyEx('Northwind','collation')

Knowledge Base

Search Knowledge Base