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
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])
CREATE NONCLUSTERED INDEX [IX_Employee_Surname]
ON [dbo].[Employee]([Surname] ASC);
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
)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]
ALTER Trigger [dbo].[tr_Employee_Update] ON [dbo].[Employee]
--Audit Entity ID for Employees
Declare @AuditEntityID int
set @AuditEntityID = 2
(ID,AuditEntityID,AuditTypeID, Note, Created, OperatorIDCreated)
'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)))),
Inner Join deleted on inserted.EmployeeID = deleted.EmployeeID
Where deleted.Title <> inserted.Title or deleted.Forename <> inserted.Forename or deleted.Surname <> inserted.Surname
Open in new window