Search code examples
sql-servernvarchar

SQL Server: DECRYPTBYKEY() causing 'String or binary data would be truncated.'

I'm running a SELECT query which is decrypting a field via DECRYPTBYKEY() however I keep running into the error 'String or binary data would be truncated.'

Here's my query:

SELECT CONVERT(NVARCHAR(MAX), DECRYPTBYKEY(emailBody)) as emailBody FROM EmailLog

I've taken a look around online and it seems that the problem is the decrypted string is too large for NVARCHAR to handle. Would anyone be able to confirm that or even better - offer a solution? I've been stuck on this for hours now so any help would be greatly appreciated.

The field emailBody is stored as NVARCHAR(MAX) and the database collation is Latin1_General_CI_AI_KS_WS.

UPDATE: In the end it turns out that there were a few rows in this table that weren't actually encrypted. It was these rows that caused the error to happen once DECRYPTBYKEY() was ran on the results.

Solution

  • If you read the MSDN documentation for DECRYPTBYKEY() . The return Data type of this function is VARBINARY(8000) since you have data stored in NVARCHAR(MAX) there maybe some data which might be a lot bigger than 8000 bytes. Therefore the error message.

    An ugly work around would be to let the data be truncate and suppress the error for data truncation by using the following statement before retrieving data;

    SET ANSI_WARNINGS OFF; 

SELECT CONVERT(NVARCHAR(MAX), DECRYPTBYKEY(emailBody)) as emailBody 
FROM EmailLog;