One thing that is not clear from MSDN documentation is the limits of 8000 byte for query input of EXEC and SP_ECECUTESQL. If you execute this query:

declare @query1 as varchar(max)
 set @query1= replicate(' ',7951)+ ' select top 1 object_id,name from sys.all_columns'
 exec (@query1)

declare @query2 as nvarchar(max)
 set @query2= replicate(N' ',3951)+ N' select top 1 object_id,name from sys.all_columns'
 EXECUTE sp_executesql @query2

results to

object_id name
 ----------- ----------
 1307151702 ABI

(1 row(s) affected)

object_id name
 ----------- ----------
 1307151702 ABI

(1 row(s) affected)

you get some row. The first query is composed by 8000-len(‘ select top 1 object_id,name from sys.all_columns’) blanck charcters followed by simple select. In the second query there are 3951 blank unicode characters.

If you execute:

declare @query1 as varchar(max)
 set @query1= replicate(' ',7952)+ ' select top 1 object_id,name from sys.all_columns'
 exec (@query1)

declare @query2 as nvarchar(max)
 set @query2= replicate(N' ',3952)+ N' select top 1 object_id,name from sys.all_columns'
 EXECUTE sp_executesql @query2

you get

Msg 208, Level 16, State 1, Line 1
 Invalid object name 'sys.all_column'.
 Msg 208, Level 16, State 1, Line 1
 Invalid object name 'sys.all_column'.

One more black character is enough to owerflow input buffer, and query truncation happen.