SQL Server Always Encrypted: Operand type clash: varchar is incompatible with varchar(max)
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.
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]));GOCREATE NONCLUSTERED INDEX [IX_Employee_Surname]ON [dbo].[Employee]([Surname] ASC);GOCREATE 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 UPDATEAS--Audit Entity ID for EmployeesDeclare @AuditEntityID intset @AuditEntityID = 2Insert 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.OperatorIDUpdatedFrom inserted Inner Join deleted on inserted.EmployeeID = deleted.EmployeeIDWhere deleted.Title <> inserted.Title or deleted.Forename <> inserted.Forename or deleted.Surname <> inserted.Surname
I found that shortly after posting the question, so... can I send the values to a stored procedure that will do the necessary? If so, what special conditions do I need to follow?
If I cant use a trigger to update an audit table then I need to have stored procedures to update the necessary tables and pass through parameterised information.
Raja Jegan R
Yes, that's correct. You can try with that approach..
I am getting the same issue where I am getting the type mismatch so thats not working through SQL Server.
Raja Jegan R
Can you post the Procedure Script so that I can help verify it once..
Raja Jegan R
Hi Simon,
Kindly let us know whether you are able to get through the procedure or still facing issues.
If so, then please share the scripts you were working on..