size limit for nvarchar(max)

Question

i am trying to execute a sql query string which has thousands of characters in t-sql. data type using to hold this query is nvarchar(max). but using this i can hold only 67594 characters. but my query has more characters than this. does anybody has any idea why nvarchar(max) is holding up to 67594 characters only? the nvarchar(max) should hold up to 2GB data. isn't it?

the procedure which i am running is:

create procedure test
(
@snapshotid1 nvarchar(10),
@snapshotid2 nvarchar(10),
@version1 nvarchar(100),
@version2 nvarchar(100)
) AS DECLARE
@sql nvarchar(max),
@whereClause1 nvarchar(500),
@whereClause2 nvarchar(500),
@fromClause1 nvarchar(100),
@fromClause2 nvarchar(100)

BEGIN
set @sql = '';
set @sql = @sql + N'    
    select v1.v1_brand, version1total, version2total, version1total - version2total as varience from (
    select   "C - Brand" as v1_brand, 

    case ' + @period + ' when ''Jan'' then sum(Period1InvoicedAmount)
    when ''Feb'' then
.
.
.

END

regards

Subash Amara

duplicate of http://stackoverflow.com/questions/2712336/what-is-the-maximum-length-of-a-string-parameter-to-stored-procedure — , Aug 13 '13 at 09:06
Check this http://stackoverflow.com/questions/12639948/sql-nvarchar-and-varchar-limits — Vijay Hulmani, Aug 13 '13 at 09:06
sorry this is not exactly duplicate of the above topics. i am concatenating nvarchar(max) strings only. so it shouldn't get truncated. — subash, Aug 13 '13 at 09:19

score 1 · Answer 1 · answered Aug 13 '13 at 09:31

Can't reproduce. But! don't think a proper TSQL concatenation can't handle large string. You probably have a problem somewhere in your script. It depends what error are you getting.

Here's a little proof of concept you can run.

First write this script:

DECLARE @sql nvarchar(max) = ''
SET @sql = N'SELECT fld = newid()'

-- (PLACEHOLDER) --

SELECT DATALENGTH(@sql)

EXEC(@sql)

Then run this script and copy the rows...

SELECT TOP 2000
  q = 'SET @sql = @sql + N''UNION ALL SELECT fld = newid()'''
FROM sys.syscolumns t1, sys.syscolumns t2

...and paste it instead of -- (PLACEHOLDER) --, so you have...

DECLARE @sql nvarchar(max) = ''
SET @sql = N'SELECT fld = newid()'

SET @sql = @sql + N'UNION ALL SELECT fld = newid()'
SET @sql = @sql + N'UNION ALL SELECT fld = newid()'
...(totals 2000 lines)...
SET @sql = @sql + N'UNION ALL SELECT fld = newid()'

SELECT DATALENGTH(@sql)

EXEC(@sql)

When you execute this, you will see that data length is 120040 (well above 67594) and it outputs 2001 rows, as it should.

However, if you try to PRINT or SELECT your dynamically created string in SSMS, like:

PRINT @sql
SELECT @sql

...you will get truncated results.

size limit for nvarchar(max)

1 Answers1