troubleshooting Question

SQL Server Always Encrypted: Operand type clash: varchar is incompatible with varchar(max)

Avatar of SimonPrice3376
SimonPrice3376 asked on
DatabasesMicrosoft SQL ServerMicrosoft SQL Server 2005Microsoft SQL Server 2008SQL
9 Comments2 Solutions3525 ViewsLast Modified:
As we have regulation changes coming in force in the UK soon a database that I am working on that needs to be updated to have any personal identifiable information encrypted.

A number of my tables have been altered successfully, however on some tables where there are triggers I am getting the following error.


Error SQL72014: .Net SqlClient Data Provider: Msg 206, Level 16, State 2, Procedure tr_Employee_Update, Line 27 Operand type clash: varchar is incompatible with varchar(max) 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 = 'xxxx') collation_name = 'Latin1_General_BIN2'

I have looked at this question here, however this doesnt solve my issue Operand type clash: varchar is incompatible with varchar(50) trying to insert in encrypted database

Same with this question too where is doesnt address my issue exactly. SQL Server Always Encrypted Operand type clash: varchar is incompatible with varchar(60) when running EXEC sproc.

I have this issue on a number of tables so would be grateful for any and all help.

Please see the SQL Fiddle here

http://sqlfiddle.com/#!18/4ac5c/3

I have had to split the table and trigger creation because the SQL length is greater than 8000 characters but this is the fullest example I can give.

I am encrypting the columns using Encryption type: Deterministic and Encryption key name: CEK_Auto1.

Not all columns in this table need encrypting and I am altering some of the other fields that have default values too where encryption does need to take place.

Any and all help on the reported issue would be gratefully received.

CREATE TABLE [dbo].[Employee] (
[EmployeeID]        INT           IDENTITY (1, 1) NOT NULL,
[EmployeeTypeID]    INT           NOT NULL,
[Title]             VARCHAR (50)  NOT NULL,
[Forename]          VARCHAR (30)  NOT NULL,
[Surname]           VARCHAR (30)  NOT NULL,
[AddressLine1]      VARCHAR (60)  NOT NULL,
[AddressLine2]      VARCHAR (60)  NOT NULL,
[AddressLine3]      VARCHAR (60)  NOT NULL,
[AddressLine4]      VARCHAR (60)  NOT NULL,
[Town]              VARCHAR (50)  NOT NULL,
[County]            VARCHAR (50)  NOT NULL,
[PostCode]          VARCHAR (20)  NOT NULL,
[Phone]             VARCHAR (20)  CONSTRAINT [DF_Employee_Phone] DEFAULT ('') NOT NULL,
[Mobile]            VARCHAR (20)  NOT NULL,
[Fax]               VARCHAR (20)  NOT NULL,
[Email]             VARCHAR (50)  NOT NULL,
[Extension]         VARCHAR (10)  CONSTRAINT [DF_Employee_Extension_1] DEFAULT ('') NOT NULL,
[SpeedDial]         VARCHAR (10)  CONSTRAINT [DF_Employee_SpeedDial_1] DEFAULT ('') NOT NULL,
[Notes]             VARCHAR (MAX) NOT NULL,
[EmployeeTeamID]    INT           NULL,
[Created]           DATETIME      CONSTRAINT [DF_Employee_Created] DEFAULT (getdate()) NOT NULL,
[OperatorIDCreated] INT           NOT NULL,
[Updated]           DATETIME      CONSTRAINT [DF_Employee_Updated] DEFAULT (getdate()) NOT NULL,
[OperatorIDUpdated] INT           NOT NULL,
[Deleted]           BIT           CONSTRAINT [DF_Employee_Deleted] DEFAULT ((0)) NOT NULL,
[EmployeeIDManager] INT           NULL,
[JobTitle]          VARCHAR (100) CONSTRAINT [DF_Employee_JobTitle] DEFAULT ('') NOT NULL,
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED ([EmployeeID] ASC),
CONSTRAINT [FK_Employee_Employee] FOREIGN KEY ([EmployeeIDManager]) REFERENCES [dbo].[Employee] ([EmployeeID]),
CONSTRAINT [FK_Employee_EmployeeTeam] FOREIGN KEY ([EmployeeTeamID]) REFERENCES [dbo].[EmployeeTeam] ([EmployeeTeamID]),
CONSTRAINT [FK_Employee_EmployeeType] FOREIGN KEY ([EmployeeTypeID]) REFERENCES [dbo].[EmployeeType] ([EmployeeTypeID])
);


GO
CREATE NONCLUSTERED INDEX [IX_Employee_Surname]
ON [dbo].[Employee]([Surname] ASC);


GO

CREATE TABLE [dbo].[AuditItem](
[AuditItemID] [INT] IDENTITY(1,1) NOT NULL,
[ID] [INT] NOT NULL,
[AuditEntityID] [INT] NOT NULL,
[AuditTypeID] [INT] NOT NULL,
[Note] [VARCHAR](MAX) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = DETERMINISTIC, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL,
[Created] [DATETIME] NOT NULL,
[OperatorIDCreated] [INT] NOT NULL,
[ProfessionalIDCreated] [INT] NULL,
 CONSTRAINT [PK_AuditItem] PRIMARY KEY CLUSTERED 
(
[AuditItemID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

    ALTER Trigger  [dbo].[tr_Employee_Update] ON [dbo].[Employee] 
FOR UPDATE

AS

--Audit Entity ID for Employees
Declare @AuditEntityID int
set @AuditEntityID = 2

Insert AuditItem
    (ID,AuditEntityID,AuditTypeID, Note, Created, OperatorIDCreated)
Select
    inserted.EmployeeID,
    @AuditEntityID,
    --Update type
    2,
    'Name changed from ' + ltrim(rtrim(ltrim(rtrim(Deleted.Title)) + ' ' + ltrim(rtrim(Deleted.Forename)) + ' ' + ltrim(rtrim(Deleted.Surname)))) + ' to ' + + ltrim(rtrim(ltrim(rtrim(Inserted.Title)) + ' ' + ltrim(rtrim(Inserted.Forename)) + ' ' + ltrim(rtrim(Inserted.Surname)))),
    GetDate(),
    inserted.OperatorIDUpdated
From inserted
        Inner Join deleted on inserted.EmployeeID = deleted.EmployeeID
Where deleted.Title <> inserted.Title or deleted.Forename <> inserted.Forename or deleted.Surname <> inserted.Surname
SOLUTION
Join our community to see this answer!
Unlock 2 Answers and 9 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 9 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros