nvarchar(MAX) limit

Question

nvarchar(MAX) limit

suhas.wadadekar

Ten Centuries

Points: 1219
More actions
July 21, 2008 at 12:57 pm

#210245

Hi,
I am trying to use nvarchar(MAX) variable for one of my dynamic sql query. However, the query string is getting chopped off after 4000 characters.
I don't understand why this should happen when I am using nvarchar(MAX)?
Thanks,
Suhas.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

Are you using a select nvarchar(max) in SSMS to try to debug/check the dynamic sql string? Perhaps the UI Is truncating it.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

suhas.wadadekar Ten Centuries Points: 1219 More actions · Answer 2

somewhat similar...

instead of executing

exec(@sql)

i am doing

print @sql

Also I printed out len(@sql) which returns me 4000

(@sql is declared as nvarchar(MAX))

antonio.collins SSCrazy Eights Points: 9600 More actions · Answer 3

how are you building your string? if you're attempting to concatenate 'regular' varchars together, they'll max out at 8000 characters regardless of what you're trying to stuff them into.

declare @sql nvarchar(max), @fragment varchar(3000)

set @fragment = replicate( 'long text!', 300 )

print len(@fragment) -- 3000

set @sql = @fragment + @fragment + @fragment

print len(@sql) -- 8000

set @sql = cast(@fragment as nvarchar(max)) + cast(@fragment as nvarchar(max)) + cast(@fragment as nvarchar(max))

print len(@sql) -- 9000

suhas.wadadekar Ten Centuries Points: 1219 More actions · Answer 4

I think this is the problem...

i am trying to insert a few dates into the query. In the process I use this statement:

@sql = @sql + cast(@date as varchar)

this points to exactly what you mentioned. I'll try out using all string variable of the type nvarchar(MAX) and report the results....

Thanks,

Suhas.

suhas.wadadekar Ten Centuries Points: 1219 More actions · Answer 5

yes it did work out... thanks Jack, Antonio for your help

i turned all string variables related to the dynamic query variable (@sql) to nvarchar(MAX) and i am getting the results....

did not think that mixing varchar and nvarchar(MAX) could cause this...good thing to know.

on a separate note...i continued printing the @sql constructed query and it's length.....i dont understand why the query still looks truncated (it works though) and length is 4195....

is there a limit on how many characters can be displayed by print command?

antonio.collins SSCrazy Eights Points: 9600 More actions · Answer 6

print is limited to 8K varchar and 4K nvarchar (since nvarchar is double byte).

PRINT msg_str | @local_variable | string_expr

Arguments

msg_str

Is a character string or Unicode string constant. For more information, see Constants (Transact-SQL).

@local_variable

Is a variable of any valid character data type. @local_variable must be char or varchar, or it must be able to be implicitly converted to those data types.

string_expr

Is an expression that returns a string. Can include concatenated literal values, functions, and variables. The message string can be up to 8,000 characters long; any characters after 8,000 are truncated. For more information, see Expressions (Transact-SQL).

masthan.yenumula Valued Member Points: 72 More actions · Answer 7

print like this. it will work

print substring(@sql,1,3999)

print substring(@sql,4000,8000)

........