you probably appended a non varchar(max) string to a varchar(max) string which caused the truncation. be sure all literals and intermediate string are cast to varchar(max).
Assigning large text block to nvarchar(max) SOMETIMEs truncates at 4000 bytes
I have a really large dynamic sql query (19,000+ in length) that is assigned to @SQLText nvarchar(max). Everything was working. Made some minor changes and then @SQLText variable only contained 4000 characters, truncating the rest of the query text. Split the query into @SQLText1 and @SQLText2 - both defined nvarchar(max). Now SQLText1 is 14,650 in length and SQLText2 is 4000 in length. @SQLText1 is way over 4000 and @SQLText2 is only 4000 and truncating some of the text. I do not want to make this dynamic sql any more complicated than it already is by chunking it up. My question is what is making the Text sometimes truncate and other times not truncating when assigned to a nvarchar(max)? I also worry that even if I get one variable to hold the entire 19K text that possible on other production sql servers it might truncate?
SQL Server | SQL Server Transact-SQL
-
Bruce (SqlWork.com) 82,146 Reputation points Volunteer Moderator
2025-07-14T15:45:47.67+00:00