Operand type clash: varchar is incompatible with varchar(100) encryptedwith (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256',
Question
Thursday, January 11, 2018 10:05 AM | 1 vote
Hi,
While Creating Store Procedure on encrypted column, getting below mentioned error. Someone please guide me how we can create stored procedure, OR Remove Collation from respective column?
Is there any implication, if we removed collation from columns ? how we can handle this in Encryption. ?
How to handle this.
Please suggest.
Operand type clash: varchar is incompatible with varchar(100) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'CompanyName') collation_name = 'Latin1_General_BIN2'
R,
Abhishesh Pandey
All replies (5)
Friday, January 12, 2018 5:45 AM
Hi Abhishesh Pandey,
Not sure what your code looks like, but it seems that you are trying to perform update/insert on encrypted columns, right? In this case, the data must be inserted/updated using parameterized query, and you cannot do it within SSMS. For more information, plesea refer to this blog.
If you have any other questions, please let me know.
Regards,
Lin
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
Wednesday, December 5, 2018 2:16 AM
I've the same issue while creating a stored procedure which refers the encrypted column, any help is appreciated.
Friday, January 11, 2019 7:25 AM
Hi Abhishek,
It looks like you are using parameters in the Stored Proc. The limitation with always encrypt is that the data type of the SQL param must match exactly with the data type of the column.
In your case you are using varchar as parameter in sp where as the column is of type varchar(100).
Please change the SQL parameter type to VARCHAR 100.
Note: the length also must be same, in your case VARCHAR 100 will work where as varchar 200 will not due to different size.
I hope that answers your question.
Please mark this as answer if if solves your problem
MSDN Community Supp
Tuesday, August 20, 2019 9:56 AM
HI Mubahil,
Thanks for Suggestion, Have tried that as well but again back to same issue.
Just to brief on mentioned ticket, My actual table is having columns which is Encrypted and was trying to create a Variable table and inserting Encrypted column data into one of the column created in Variable table.
Code Snippet :
(Physical table)
CREATE TABLE [DBO].[CandidateDetails]
(
[CandidateName] [varchar](500) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL
)
(Variable table)
DECLARE @Candidate AS TABLE
(
[CandidateName] [varchar](500) COLLATE Latin1_General_BIN2
)
While Trying to Insert Columns value from Physical table to Variable table getting the mentioned Error. Please Suggest how to handle this.
Exceptionally, Just to resolve the issue, Have removed Variable table and Created Another Physical table and copied all similar Data type Properties from 1st Table and It Works.
Somehow in this case, That solution works but still in confusion why it is not working with Variable table ? OR Encryption Does not support Variable Table ? Is there any other method to use Variable table ?
Regards,
Abhishesh Pandey
Wednesday, August 21, 2019 9:54 PM
Just to brief on mentioned ticket, My actual table is having columns which is Encrypted and was trying to create a Variable table and inserting Encrypted column data into one of the column created in Variable table.
You can't do that. The value for the encrypted table-column is to be enscypted client-side, so you can't take an unencrypted value and stored it into the encrypted column.
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se