-2

I am using SSMS 17.6 and I am trying out the Always Encrypted feature. I was able to update/insert/search a datetime field by declaring a parameter and then using the parameter, e.g.:

DECLARE @NewValue datetime 
SET @NewValue = '19/05/2018' 
update MyTable set my_ae_column = @NewValue

I am trying doing the same for a varchar(30) field for which bin2 collation is set, but I still get the

Operand type clash issue

Any suggestion?

  • 1
    You need to do a bunch of things first. Have you followed every single thing in this guide? – Aaron Bertrand May 19 '18 at 23:04
  • @AaronBertrand Yes, I did, that's why I was able to update a datetime encrypted column. My issue is with the varchar column (also encrypted). – Manu May 19 '18 at 23:07
  • 2
    Show your code that fails (instead of the code that works!) and the table structure. If you just use varchar for example it's going to break; if you use varchar(10) but the column is varchar(20) it's going to break; if you use DECLARE and SET separately (like in your question) it's going to break. – Aaron Bertrand May 19 '18 at 23:15
  • The structure is varchar(30) as well as the declared variable...mindblowing – Manu May 19 '18 at 23:17
  • 2
    Again, show your code, and we might be able to help identify what is wrong. If you're not willing to show your code, we have to guess at a possible answer, like I did... ¯\_(ツ)_/¯ – Aaron Bertrand May 20 '18 at 0:33
5

My guess is that you are not checking the following from this guide, which states that you can't use DECLARE and SET separately:

Declared and initialized in the same statement (inline initialization). SSMS will not parameterize variables declared using separate SET statements.

I get the same error you get when I write code like what you have in the question. This is why we ask for the code that breaks, not the code that works (though I doubt the code you wrote in your question works either!):

DECLARE @column nvarchar(30);
SET @column = N'whatever';
UPDATE ...

It works just fine when I declare the variable and assign it immediately, like this:

DECLARE @column nvarchar(30) = N'whatever';
UPDATE ...

This mismatch can happen for a variety of other reasons, of course. Such as not setting your connection to column encryption setting = enabled, not enabling the "Enable Paramterization..." setting in SSMS, using a function or expression when declaring and assigning the variable, using the wrong data type or length or precision or scale, etc. We could help narrow that down if you include the code that breaks.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.