How can I output more than 256 characters to a file?
While trying out the various suggestions in the comments I have stumbled across a solution.

I replaced:

'"' + Column4 + '"' as ReallyLongString,


with:

'"' + REPLICATE('O', 4000) + '"' AS ReallyLongString,


and all of the text was output.

However, using a variable:

DECLARE @longText NVARCHAR(MAX)
SET @longText = REPLICATE('O', 4000);

'"' + @longText + '"' AS ReallyLongString,


didn't.

What did work was declaring the variable with a specific length:

DECLARE @longText NVARCHAR(4000)


So, as the text in this case is never going to be longer than 2000 characters (the device reading the file can't cope with text that long) I convert all the text to 2000 character long strings:

SELECT
    'A' as First,
    Column1 as ABC,
    REPLACE(FORMAT(Column2, 'N2', 'en-GB'), ',', '') as Number, -- to get the format correct
    '"' + CAST(Column3 as NVARCHAR(2000)) + '"' as String,
    '"' + CAST(Column4 as NVARCHAR(2000)) + '"' as ReallyLongString,
    ....
FROM Table
WHERE &lt;condition&gt;


This is the how. I still don't really know the why.