Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

nvarchar(MAX) limit Expand / Collapse
Author
Message
Posted Monday, July 21, 2008 12:57 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, January 14, 2010 2:35 PM
Points: 25, Visits: 53
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.
Post #537926
Posted Monday, July 21, 2008 1:08 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Wednesday, December 24, 2014 10:57 AM
Points: 10,387, Visits: 13,454
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #537933
Posted Monday, July 21, 2008 1:11 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, January 14, 2010 2:35 PM
Points: 25, Visits: 53
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))
Post #537934
Posted Monday, July 21, 2008 1:15 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 8:52 AM
Points: 438, Visits: 909
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

Post #537937
Posted Monday, July 21, 2008 1:23 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, January 14, 2010 2:35 PM
Points: 25, Visits: 53
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.
Post #537944
Posted Monday, July 21, 2008 1:32 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, January 14, 2010 2:35 PM
Points: 25, Visits: 53
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?
Post #537949
Posted Monday, July 21, 2008 1:41 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 8:52 AM
Points: 438, Visits: 909
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).
Post #537952
Posted Thursday, November 22, 2012 4:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 13, 2014 7:19 AM
Points: 4, Visits: 48
print like this. it will work
print substring(@sql,1,3999)
print substring(@sql,4000,8000)
........
Post #1387830
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse