The curious case of the truncating NVARCHAR(MAX) variable

This weekend, while working on a long overdue PSBlitz update, I’ve ran into an interesting issue that ended up truncating an NVARCHAR(MAX) variable to 4000 characters.

Intro

The code in this case is the GetStatsInfoForWholeDB.sql script that’s part of PSBlitz’s resources.
This script is used for, you wouldn’t believe by the name alone, getting statistics information for a specific database.

Due to the fact that it might be ran on Azure or on older versions of SQL Server, as well as on databases with incremental statistics, the best option for it was to use dynamic SQL.

In this case it uses a variable @SQL defined as NVARCHAR(MAX) to store the query that’s built at runtime and execute it via EXEC.

What’s dynamic SQL

To keep things short; dynamic SQL relies on building your query or any other T-SQL command as a string variable and then passing it to either EXEC if it’s fairly simple, or to sp_executesql if it needs parameters, and you’re trying to avoid creating an opportunity for SQL injection.

It’s great for cases where you’re building a query whose structure depends a lot on the type of environment it’s being executed against.

The problem

I add a new column to the query as part of issue 207, and my initial test run ended up with an error:

Msg 105, Level 15, State 1, Line 86
Unclosed quotation mark after the character string ‘s’.
Msg 102, Level 15, State 1, Line 86
Incorrect syntax near ‘s’.

Troubleshooting

A bit of perquisite information about the script

The version that was erroring out can be found here.

These are the relevant variables:

5 lines worth of example of how the @SQL string is built:

And it’s executed like this:

Moving on

Since the dynamic SQL contains string values inside, I figured that somewhere I might have missed an apostrophe.

The easiest way to see how the “finished” query looks like is to switch to Results To Text (Ctrl+T) and change EXEC(@SQL) into SELECT @SQL.

I do this as a workaround for the 4000 Unicode character limit that PRINT has. Note that there’s also an SSMS setting that needs to be changed in order to see more than 256 characters worth of text per column in results to text.

I see in the result that my query is cut off and is missing the last 8 lines.

At this point, I decide to check the length of the @SQL variable.
To do this, I change SELECT @SQL into SELECT LEN(@SQL).

And the result is a round 4000.

Failed fixing attempts

My initial suspicion is that the string loaded into the @LineFeed variable ends up being VARCHAR and causes an implicit conversion of the string loaded into @SQL.

I explicitly cast the value loaded into @LineFeed and rerun the script.

The result is the same – 4000.

I then start going through the string concatenation suspecting that I might have missed a preceding N from a string delimiter. Accidentally causing an implicit conversion that would somehow cause the truncation.
But this wasn’t the case.

Even @MinRecords is correctly converted to NVARCHAR when appended to @SQL‘s contents.

At this point I was certain that I was missing something, but I really couldn’t tell what that was.

As far as I could tell, everything was correct and there was no valid reason for @SQL to be anything else than NVARCHAR(MAX), yet something was still truncating it to 4000 characters.

Since I was all out of ideas, I did what everyone does, I Googled 🙂

I ran into this SQLServerCentral forum post from 2010 describing the exact same issue I was running into and Eddie Wuerch’s response explained both the cause and the fix.

So what ends up truncating NVARCHAR(MAX)

According to Eddie’s response, the base string added to the variable defaults to NVARCHAR(4000) unless it’s explicitly converted to NVARCHAR(MAX).
This forces the variable to act as NVARCHAR(4000) even if it’s declared as NVARCHAR(MAX).

The fix

I do the following change to the first line loaded into the @SQL variable:

Now the length of the @SQL variable’s content is 4255.

I switch to select to confirm the whole query is there, and indeed, it looks as expected.

Conclusion

TIL.

Also, everyone Googles stuff, no matter how much experience they might think they have with something 🙂