Andrew Arnold Andrew Arnold - 2 years ago 275
SQL Question

nvarchar(max) still being truncated

So I'm writing a stored procedure in MS SQL Server 2008. It's a really long query and I have to write it dynamically, so I create a variable called

and make it of type
. Now, I have been told that in modern versions of SQL Server,
can hold a ridiculous amount of data, way more than the original 4000 character maximum. However,
is still getting truncated to 4000 characters when I try to print it out.

SET @Query = 'SELECT...' -- some of the query gets set here
SET @Query = @Query + '...' -- more query gets added on, etc.

-- later on...
PRINT LEN(@Query) -- Prints out 4273, which is correct as far as I can tell
PRINT @Query -- Truncates value to 4000 characters
EXEC sp_executesql @Query -- totally crashes due to malformed (truncated) query

Am I doing something incorrectly, or am I completely wrong about how

Answer Source

To see the dynamic SQL generated, change to text mode (shortcut: Ctrl-T), then use SELECT

PRINT LEN(@Query) -- Prints out 4273, which is correct as far as I can tell

As for sp_executesql, try this (in text mode), it should show the three aaaaa...'s the middle one being the longest with 'SELECT ..' added. Watch the Ln... Col.. indicator in the status bar at bottom right showing 4510 at the end of the 2nd output.

declare @n nvarchar(max)
set @n = REPLICATE(convert(nvarchar(max), 'a'), 4500)
SET @N = 'SELECT ''' + @n + ''''
print @n   -- up to 4000
select @n  -- up to max
exec sp_Executesql @n
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download