Operand type clash when use Always Encrypted

Question

Operand type clash when use Always Encrypted

Andrei Tselichtchev 30

When I connect SSMS to a SQL Server 2022 database with Always Encrypted enabled, I get the following error: "Msg 206, Level 16, State 2, Line 3 - Operand type clash: datetime is incompatible with uniqueidentifier."

This occurs with the code below:

DECLARE @Date AS DateTime = GETDATE(); 
DECLARE @Id AS uniqueidentifier;

However, if I change the order of the declarations, the error disappears.

Accepted answer

1 additional answer

Your answer

Answer 1

That appears to be a bug in SSMS. Using Profiler, I can see that what SSMS submits is this:

DECLARE @Date AS DateTime = GETDATE(); 
DECLARE @Id AS uniqueidentifier = GETDATE();

For this to happen, two things needs to be fulfilled:

First, you have enabled parameterisation for Always Encrypted:

User's image

Second, when you connected, you enabled Always Encrypted in the connection string:

User's image

I tested this with SSMS 21, Preview 3, and the issue persists there as well.

I would suggest that you report the issue on the feedback site: https://feedback.azure.com/d365community/forum/04fe6ee0-3b25-ec11-b6e6-000d3a4f0da0

Please share the link so that others can vote for it too.

Answer 2

LiHongMSFT-4306 31,571

Hi @Andrei Tselichtchev

Just as Erland answered above, it looks like a bug.

And tested that declaration without setting @Date = Getdate() works well.

DECLARE @Date AS DateTime; 
DECLARE @Id AS uniqueidentifier;
SET @Date = GETDATE();

Best regards,

Cosmog

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Share via

Operand type clash when use Always Encrypted

1 additional answer

Your answer