why it is not possible to store more than 8000 bytes of data in nvarchar(max) variable?

Question

why it is not possible to store more than 8000 bytes of data in nvarchar(max) variable?

Ajdba

Ten Centuries

Points: 1051
More actions
September 10, 2012 at 10:24 am

#261304

Hi,
I have a variable of @sql_str nvarchar(max) and it is giving me error saying '...it is too long and maximum length is 8000'; when I do select LEN(@sql_str), I get the length of 41734.
I have done so much googling and everywhere it is saying it is possible to store more than 8000 characters in nvarchar(max) variable; am I missing something??
Appreciate the suggestions.
Thanks
Aj

Viewing 10 posts - 1 through 9 (of 9 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Ajdba (9/10/2012)
Hi,
I have a variable of @sql_str nvarchar(max) and it is giving me error saying '...it is too long and maximum length is 8000'; when I do select LEN(@sql_str), I get the length of 41734.
I have done so much googling and everywhere it is saying it is possible to store more than 8000 characters in nvarchar(max) variable; am I missing something??
Appreciate the suggestions.
Thanks
Aj

Not enough context to give you an answer. What are you trying to accomplish?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Post your code. Somewhere you're converting to VARCHAR(8000) or NVARCHAR(4000). Nvarchar(max) can store up to 2GB of data.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ajdba Ten Centuries Points: 1051 More actions · Answer 3

Basically, this is the sql string I am trying to pass where I am getting the error;

@sql_str_gis nvarchar(max)

@lstStr nvarchar(max)

SELECT @sql_str_gis = N'SELECT IncidentIdNbr, incident_year, incident_date, incident_time ,county_desc, routetype_desc, b.route_number,

b.beg_measure, intersection_rttype_desc, intersection_route, InjuriesNbr_Total,FatalitiesNbr_Total, totalNbrOfVehicle,

firstharmfulevent_desc, mannerofcollision_desc, locationatimpact_desc, lightcondition_desc, surfacecondition_desc,

RampSection, directionnmovement1_desc,directionnmovement2_desc, directionnmovement3_desc, vehiclemaneuver1_desc,vehiclemaneuver2_desc,

vehiclemaneuver3_desc, PDF_LINK, b.AADT_TOTAL as VMT, null as ORIGINALROUTE, b.end_measure

from VW_GEARS_REPORT_DATA a

INNER JOIN VW_FRED_AADT_HIST b on a.RCLINK = b.RCLINK and a.incident_year = b.YEAR

WHERE a.LatDecimal IS NOT NULL

and a.LongDecimal IS NOT NULL

and IncidentIdNbr in (' + @listStr + ')

and incident_year in ' + @Years + ''

SELECT @sql_str = N' SELECT * from OPENQUERY(EDWGEARS, ''' + REPLACE(@sql_str_gis, '''', '''''') + ''')'

select LEN(@sql_str)

Ajdba Ten Centuries Points: 1051 More actions · Answer 4

I am not understanding if all my string variables are nvarchar(max) , then what could be reason for getting the error...really confused..

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

What are these:

@listStr

@Years

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ajdba Ten Centuries Points: 1051 More actions · Answer 6

Ajdba

Ten Centuries

Points: 1051

September 10, 2012 at 11:25 am

#1535843

Both @listStr, @Years are nvarchar(max)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

The query string for OPENQUERY is restricted to 8KB, for BOL:

'query'
Is the query string executed in the linked server. The maximum length of the string is 8 KB.

http://msdn.microsoft.com/en-us/library/ms188427(v=sql.100).aspx

Ajdba Ten Centuries Points: 1051 More actions · Answer 8

aghhhh..that makes sense.....

can you suggest any alternate in this scenario...

Ajdba Ten Centuries Points: 1051 More actions · Answer 9

Here is what I am thinking of doing; how about breaking the queries into three parts as below-

SELECT @listStr1 = SUBSTRING((COALESCE(@listStr+',' ,'')),1,6000) + Incident FROM @IncidentGIS

SELECT @listStr2 = SUBSTRING((COALESCE(@listStr+',' ,'')),6001,6000) + Incident FROM @IncidentGIS

SELECT @listStr3 = SUBSTRING((COALESCE(@listStr+',' ,'')),12001,6000) + Incident FROM @IncidentGIS

Then send break the openquery into 3 parts using @listStr1, @listStr2, @listStr3 ; but here is the dilemma. In my @listStr1 there is a comma after the incidentnumber (i.e, 1234567,2367895...so forth), but when I am doing substring(@listStr,1,6000) , I am getting the numbers before the comma. How can I make sure that I get the whole numbers(7 digits) within the substring.