title: SQL Server Always Encrypted: Operand type clash: varchar is incompatible with varchar(max)
body: <p>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. </p>
  
  <p>A number of my tables have been altered successfully, however on some tables where there are triggers I am getting the following error. </p>
  
  <blockquote>
    <p>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'</p>
  </blockquote>
  
  <p>I have looked at this question here, however this doesnt solve my issue <a href="https://stackoverflow.com/questions/40266502/operand-type-clash-varchar-is-incompatible-with-varchar50-trying-to-insert-in">Operand type clash: varchar is incompatible with varchar(50) trying to insert in encrypted database</a> </p>
  
  <p>Same with this question too where is doesnt address my issue exactly.  <a href="https://stackoverflow.com/questions/47415873/sql-server-always-encrypted-operand-type-clash-varchar-is-incompatible-with-var">SQL Server Always Encrypted Operand type clash: varchar is incompatible with varchar(60) when running EXEC sproc</a>.</p>
  
  <p>I have this issue on a number of tables so would be grateful for any and all help. </p>
  
  <p>Please see the SQL Fiddle here </p>
  
  <p><a href="http://sqlfiddle.com/#!18/4ac5c/3" rel="nofollow noreferrer">http://sqlfiddle.com/#!18/4ac5c/3</a></p>
  
  <p>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. </p>
  
  <p>I am encrypting the columns using Encryption type: Deterministic and Encryption key name: CEK_Auto1. </p>
  
  <p>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. </p>
  
  <p>Any and all help on the reported issue would be gratefully received. </p>
  
  <pre><code>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 &lt;&gt; inserted.Title or deleted.Forename &lt;&gt; inserted.Forename or deleted.Surname &lt;&gt; inserted.Surname
  </code></pre>