nvarchar(max) still being truncated

Problem seems to be associated with the SET statement. I think the expression can't be more than 4,000 bytes in size. There is no need to make any changes to any settings if all you are trying to do is to assign a dynamically generated statement that is more than 4,000 characters. What you need to do is to split your assignment. If your statement is 6,000 characters long, find a logical break point and then concatenate second half to the same variable. For example:

SET @Query = 'SELECT ....' [Up To 4,000 characters, then rest of statement as below]

SET @Query = @Query + [rest of statement]

Now run your query as normal i.e. EXEC ( @Query )

The problem is with implicit conversion.

If you have Unicode/nChar/nVarChar values you are concatenating, then SQL Server will implicitly convert your string to nVarChar(4000), and it is unfortunately too dumb to realize it will truncate your string or even give you a Warning that data has been truncated for that matter!

When concatenating long strings (or strings that you feel could be long) always pre-concatenate your string building with CAST('' as nVarChar(MAX)) like so:

SET @Query = CAST('' as nVarChar(MAX))--Force implicit conversion to nVarChar(MAX)
           + 'SELECT...'-- some of the query gets set here
           + '...'-- more query gets added on, etc.

What a pain and scary to think this is just how SQL Server works. :(

I know other workarounds on the web say to break up your code into multiple SET/SELECT assignments using multiple variables, but this is unnecessary given the solution above.

For those who hit an 8000 character max, it was probably because you had no Unicode so it was implicitly converted to VarChar(8000).

Explanation:
What's happening behind the scenes is that even though the variable you are assigning to uses (MAX), SQL Server will evaluate the right-hand side of the value you are assigning first and default to nVarChar(4000) or VarChar(8000) (depending on what you're concatenating). After it is done figuring out the value (and after truncating it for you) it then converts it to (MAX) when assigning it to your variable, but by then it is too late.

To see the dynamic SQL generated, change to text mode (_shortcut: Ctrl-T), then use SELECT

PRINT LEN(@Query) -- Prints out 4273, which is correct as far as I can tell
--SET NOCOUNT ON
SELECT @Query

As for sp_executesql, try this (in text mode), it should show the three aaaaa...'s the middle one being the longest with 'SELECT ..' added. Watch the Ln... Col.. indicator in the status bar at bottom right showing 4510 at the end of the 2nd output.

declare @n nvarchar(max)
set @n = REPLICATE(convert(nvarchar(max), 'a'), 4500)
SET @N = 'SELECT ''' + @n + ''''
print @n   -- up to 4000
select @n  -- up to max
exec sp_Executesql @n