nvarchar(max) - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

nvarchar(max)

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2012-01-25 : 10:32:14

Is there a limit to nvarchar(max) ?
It seems to be 4262 characters ?

I have to use this nvarchar(max) as I am doing this:

declare @sql nvarchar(max)
set @sql = 'A very long sql query'

exec sp_executesql @sql

Thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-01-25 : 10:34:24

2^31-1 Bytes

No, you're never too old to Yak'n'Roll if you're too young to die.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-01-25 : 10:41:38

the sql string you are encoding has a very, very large limit. (2^31 - 1 bytes)

However, if you screw with it with a mix of datatypes then you may get an implicit conversion down to NVARCHAR(4000) or similar

Also the PRINT command only prints a few thousand characters so you can't rely on printed output.

again -- what are you actually trying to do?

Recent posts:

global temp table
dynamic pivot
column names
very large dynamic sql string....

sounds like it's going to be messy.....

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-25 : 10:47:41

quote:
Originally posted by arkiboys

Is there a limit to nvarchar(max) ?
It seems to be 4262 characters ?

I have to use this nvarchar(max) as I am doing this:

declare @sql nvarchar(max)
set @sql = 'A very long sql query'

exec sp_executesql @sql

Thanks

how do you come to conclusion that it stores only 4262 chars? did you try printing out in which case it wont show full details as specified by Charlie.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2012-01-25 : 11:18:30

I have all the variables declared as nvarchar(max)
then using set @sql = 'very large select query'
print len(@sql) shows 4262

Kristen
Test

22859 Posts

Posted - 2012-01-25 : 11:22:46

Perhaps you need PRINT datalength(@sql) ?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-01-25 : 11:26:50

Isn't is possible that your "very large select query" has a length of 4262 or what is your problem?

No, you're never too old to Yak'n'Roll if you're too young to die.

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2012-01-25 : 11:27:27

quote:
Originally posted by Kristen

Perhaps you need PRINT datalength(@sql) ?

print @sql --> does not show all the string
print len(sql) --> shows 4262
print datalength(@sql) shows 8524

Question:
HOw can I print all the string in datalength(@sql)
Thanks

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-01-25 : 11:27:55

if you just type into a string like this



DECLARE @foo NVARCHAR(MAX)

SET @foo = N'<VERY LONG TEXT>'

Then you get an automatic conversion to NVARCHAR(4000)

If you do:

SET @foo = CAST('<VERY LONG STRING>' AS NVARCHAR(MAX)) you don't get an implicit conversion.

I just tested this.[code]
DECLARE @sql NVARCHAR(MAX) = N''

DECLARE @foo INT = 1

WHILE @foo > 0
BEGIN
	SET @sql = @sql + CAST('hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi thi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,hi there,here,hi there,hi there,hi there,hi there,hi there,hi there,there,hi there,hi there,hi there,hi there,hi there,hi there,' AS NVARCHAR(MAX))
	SET @foo = @foo - 1
END

SELECT LEN(@sql)
PRINT @sql

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-01-25 : 11:28:46

having an extremely long piece of dynamic sql is just asking for trouble.

Debugging that will be a nightmare.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-01-25 : 11:34:27

quote:
Originally posted by arkiboys

quote:
Originally posted by Kristen

Perhaps you need PRINT datalength(@sql) ?

print @sql --> does not show all the string
print len(sql) --> shows 4262
print datalength(@sql) shows 8524

Question:
HOw can I print all the string in datalength(@sql)
Thanks

Sigh

DATALENGTH(@sql) Shows 8524 *only* because a NVARCHAR stores each character as 2 bytes.

LEN(@sql) is telling you the truth - there are only 4262 characters in there.

See my previous post about implicit conversion.

I'm going to stop here because you aren't listening to any offers to change what you are doing.

Good luck with your dynamic nightmare.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2012-01-25 : 15:59:50

Solved. thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources