July 21, 2008 at 12:57 pm
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.
July 21, 2008 at 1:08 pm
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
July 21, 2008 at 1:11 pm
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))
July 21, 2008 at 1:15 pm
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
July 21, 2008 at 1:23 pm
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.
July 21, 2008 at 1:32 pm
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?
July 21, 2008 at 1:41 pm
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).
November 22, 2012 at 4:24 am
print like this. it will work
print substring(@sql,1,3999)
print substring(@sql,4000,8000)
........
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply