SimonPrice3376
asked on
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.
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.
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_data base_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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
Yes, that's correct. You can try with that approach..
ASKER
I am getting the same issue where I am getting the type mismatch so thats not working through SQL Server.
Can you post the Procedure Script so that I can help verify it once..
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..
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..
Initiating auto close as we have guided author towards the solution but haven't heard back from Author for a long time..
ASKER