Assigning large text block to nvarchar(max) SOMETIMEs truncates at 4000 bytes

Grossnickle, Brenda 160 Reputation points
2025-07-14T15:30:33.5+00:00

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
0 comments No comments
{count} votes

Answer accepted by question author
  1. Bruce (SqlWork.com) 82,146 Reputation points Volunteer Moderator
    2025-07-14T15:45:47.67+00:00

    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).


0 additional answers

Sort by: Most helpful

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.