Thank you for this clear write-up!

There is one important thing to add, which is the difference in the difference in the implementation / execution of those two operators.

UNION ALL implies...

So you are forced to write SQL shorthand...

This always bothers me when one cannot spend $...

Why didn't he simply expand the disk?

As much as one appreciates your enthusiasm replying to a 10-year-old topic, April Fool's Day might not be the right time.

On your point on recovering a corrupt Access Database, such...

Don't you absolutely love it when someone has a problem but cannot elaborate on what the problem really is?

See more: String or Binary Truncation Error Strange Behavior (SQL 2019)

He he he,...

Could you please provide more information?

Versions, Recovery Modes etc.

Can you provide the relevant table structure and an insert statement that fails?

Keep in mind that the string manipulation functions, such as LEFT,RIGHT and SUBSTRING do imply trimming of non-printable...

If it provides a good answer, especially a more effective good answer, I don't mind.  It'll help others...

SQL Server is not the right tool for this task!

The ambiguities of natural language rule out any direct string or pattern-matching approaches unless strict upstream contextual restrictions are applied, i.e.,...

What editions and versions are the servers?

Few things could be a problem, the place to start is to compare server settings, schema and then the code. Codepage could be a...

Here is a quick example that should work on all the servers

USE TEEST;
GO
SET NOCOUNT ON;
GO


-- Create a encryption key for this exercise
IF NOT EXISTS (SELECT *...

Quick thought, try using VARBINARY(MAX) for the encrypted value.

The encrypted hash is always going to be much larger than the actual input, truncated values may return NULL.

Are there any firewall TCP port restrictions?

Another thought, can anyone else access the server remotely?

Quick question, have you enabled TCP in the network settings for the server instance?