Getting Truncated Nvarchar(Max) Values

In SQL Server 2005, you may encounter an issue where Nvarchar(Max) values are only returning 4000 characters instead of the expected 8000. This truncation occurs because of a specific scenario in the provided code.

Understanding the Declaration

The variable @SQL1 is declared as NVARCHAR(Max), which allows it to store up to 2GB of data. However, this datatype is not assigned until the first assignment operation.

The Concatenation Process

In the code provided, the string is built by concatenating a series of constants and variables that are all less than 4000 characters. Before being assigned to @SQL1, the concatenated string is still considered a collection of smaller strings.

Data Type Precedence

When the assignment to @SQL1 occurs, the data type of the constant (Nvarchar(Max)) overrides the data type of the concatenated string (which is effectively Nvarchar(4000)). This results in truncating the final string to 4000 characters.

Solution

To resolve this issue, ensure that the data type of the constant being concatenated to @SQL1 is also Nvarchar(Max). This can be achieved by using the following code:

SET @SQL1 = ''
SET @SQL1 = @SQL1   'SELECT DISTINCT Venue...
  ....

By setting @SQL1 to an empty string first, the concatenation operation will always result in a Nvarchar(Max) value, preventing the truncation issue.