Expression result length exceeds the maximum????

[AndyHopper]

I'm working on a stored procedure that will split up an image into a bunch of different columns so that I can display images using a php script. This is necessary, b/c php will only return 255 characters per field.

I've been able to sucessfully generate the sql query, but it isn't executing properly. I searched the forum and google and haven't found anything pertaining to this error.

I've also been having an issue with variable scope. I need to define a variable using the EXEC(@sql) command, but it isn't letting me.

This is the error I'm getting:

Server: Msg 212, Level 16, State 1, Line 1
Expression result length exceeds the maximum. 8000 max, 8160 found.

This is the sql:

DECLARE @DataLength INT,
@bytes INT,
@i INT,
@start INT,
@sql VARCHAR(8000),
@table VARCHAR(100),
@field VARCHAR(100),
@where VARCHAR(100)

SET @table = 'NNEREN.nnerensql.dbo.idxmls1'
SET @field = 'medium_photo'
SET @where = 'id=430729'


--SET @sql = 'DECLARE @DataLength INT; SET @DataLength = (SELECT DATALENGTH(' + @field + ') as dataLength FROM ' + @table + ' WHERE ' + @where + ')'

--exec(@sql)


SET @DataLength = (SELECT DATALENGTH(medium_photo) as dataLength FROM NNEREN.nnerensql.dbo.idxmls1 WHERE id=430729)

SET @sql = 'SELECT '

SET @bytes = 0
SET @i = 0

WHILE @bytes < @DataLength
BEGIN
SET @start = @bytes
SET @bytes = @bytes + 255
SET @i = @i + 1
SET @sql = @sql + ' SUBSTRING(' + @field + ',' + convert(varchar(5),@start) + ',' +
convert(varchar(5),@bytes) + ') as col' + CONVERT(varchar(5),@i) + ','
END

SET @sql = left(@sql,len(@sql)-1)

SET @sql = @sql + ' FROM ' + @table + ' WHERE ' + @where

exec(@sql)

 

[Zathras]

Surprised this one hasn't been answered yet. Check your declaration:

DECLARE @DataLength INT,
@bytes INT,
@i INT,
@start INT,
@sql VARCHAR(8000),
@table VARCHAR(100),
@field VARCHAR(100),
@where VARCHAR(100)

 

[AndyHopper]

Thanks for the response!! It's prompts another question- The max size for a VARCHAR dataype is 8000 characters, and local variables cannot be text or ntext, so, how can I accomplish this if there are no variables large enough to hold the sql query??

Thanks in advance

 

[Zathras]

Oops! I wasn't aware of that limitation.

Someone else may have to jump in here. Have you tried doing it in parts? E.g.

Exec(@sqlA + @sqlB)

(I have no idea whether this would work or not.)

 

[sunila7]

Hi Andy,

what Zathras said should work....

Sunil

 

[AndyHopper]

After I posted my message I realized I could just concaterate the string like Zathras mentioned. I've done that, but I'm still receiving the same error.

It appears as though there's a maximum rowsize of 8000 characters.

Here's the response from the server:SELECT SUBSTRING(medium_photo,0,255) as c1, SUBSTRING(medium_photo,255,510) as c2, SUBSTRING(medium_photo,510,765) as c3, SUBSTRING(medium_photo,765,1020) as c4, SUBSTRING(medium_photo,1020,1275) as c5, SUBSTRING(medium_photo,1275,1530) as c6, SUBSTRING(medium_photo,1530,1785) as c7, SUBSTRING(medium_photo,1785,2040) as c8, SUBSTRING(medium_photo,2040,2295) as c9, SUBSTRING(medium_photo,2295,2550) as c10, SUBSTRING(medium_photo,2550,2805) as c11, SUBSTRING(medium_photo,2805,3060) as c12, SUBSTRING(medium_photo,3060,3315) as c13, SUBSTRING(medium_photo,3315,3570) as c14, SUBSTRING(medium_photo,3570,3825) as c15, SUBSTRING(medium_photo,3825,4080) as c16, SUBSTRING(medium_photo,4080,4335) as c17, SUBSTRING(medium_photo,4335,4590) as c18, SUBSTRING(medium_photo,4590,4845) as c19, SUBSTRING(medium_photo,4845,5100) as c20, SUBSTRING(medium_photo,5100,5355) as c21, SUBSTRING(medium_photo,5355,5610) as c22, SUBSTRING(medium_photo,5610,5865) as c23, SUBSTRING(medium_photo,5865,6120) as c24, SUBSTRING(medium_photo,6120,6375) as c25, SUBSTRING(medium_photo,6375,6630) as c26, SUBSTRING(medium_photo,6630,6885) as c27, SUBSTRING(medium_photo,6885,7140) as c28, SUBSTRING(medium_photo,7140,7395) as c29, SUBSTRING(medium_photo,7395,7650) as c30, SUBSTRING(medium_photo,7650,7905) as c31, SUBSTRING(medium_photo,7905,8160) as c32, SUBSTRING(medium_photo,8160,8415) as c33, SUBSTRING(medium_photo,8415,8670) as c34, SUBSTRING(medium_photo,8670,8925) as c35, SUBSTRING(medium_photo,8925,9180) as c36, SUBSTRING(medium_photo,9180,9435) as c37, SUBSTRING(medium_photo,9435,9690) as c38, SUBSTRING(medium_photo,9690,9945) as c39, SUBSTRING(medium_photo,9945,10200) as c40, SUBSTRING(medium_photo,10200,10455) as c41, SUBSTRING(medium_photo,10455,10710) as c42, SUBSTRING(medium_photo,10710,10965) as c43, SUBSTRING(medium_photo,10965,11220) as c44, SUBSTRING(medium_photo,11220,11475) as c45, SUBSTRING(medium_photo,11475,11730) as c46, SUBSTRING(medium_photo,11730,11985) as c47, SUBSTRING(medium_photo,11985,12240) as c48, SUBSTRING(medium_photo,12240,12495) as c49, SUBSTRING(medium_photo,12495,12750) as c50, SUBSTRING(medium_photo,12750,13005) as c51, SUBSTRING(medium_photo,13005,13260) as c52, SUBSTRING(medium_photo,13260,13515) as c53, SUBSTRING(medium_photo,13515,13770) as c54, SUBSTRING(medium_photo,13770,14025) as c55, SUBSTRING(medium_photo,14025,14280) as c56, SUBSTRING(medium_photo,14280,14535) as c57, SUBSTRING(medium_photo,14535,14790) as c58, SUBSTRING(medium_photo,14790,15045) as c59, SUBSTRING(medium_photo,15045,15300) as c60, SUBSTRING(medium_photo,15300,15555) as c61 FROM NNEREN.nnerensql.dbo.idxmls1 WHERE id=430729
Server: Msg 212, Level 16, State 1, Line 1
Expression result length exceeds the maximum. 8000 max, 8160 found.
Server: Msg 212, Level 16, State 1, Line 1
Expression result length exceeds the maximum. 8000 max, 8415 found.
Server: Msg 212, Level 16, State 1, Line 1
Expression result length exceeds the maximum. 8000 max, 8670 found.
Server: Msg 212, Level 16, State 1, Line 1
Expression result length exceeds the maximum. 8000 max, 8925 found.
Server: Msg 212, Level 16, State 1, Line 1
Expression result length exceeds the maximum. 8000 max, 9180 found.
Server: Msg 212, Level 16, State 1, Line 1
Expression result length exceeds the maximum. 8000 max, 9435 found.
Server: Msg 212, Level 16, State 1, Line 1
Expression result length exceeds the maximum. 8000 max, 9690 found.
Server: Msg 212, Level 16, State 1, Line 1
Expression result length exceeds the maximum. 8000 max, 9945 found.
Server: Msg 212, Level 16, State 1, Line 1
Expression result length exceeds the maximum. 8000 max, 10200 found.
Server: Msg 212, Level 16, State 1, Line 1
Expression result length exceeds the maximum. 8000 max, 10455 found.
Server: Msg 212, Level 16, State 1, Line 1
Expression result length exceeds the maximum. 8000 max, 10710 found.
Server: Msg 212, Level 16, State 1, Line 1
Expression result length exceeds the maximum. 8000 max, 10965 found.
Server: Msg 212, Level 16, State 1, Line 1
Expression result length exceeds the maximum. 8000 max, 11220 found.
Server: Msg 212, Level 16, State 1, Line 1
Expression result length exceeds the maximum. 8000 max, 11475 found.
Server: Msg 212, Level 16, State 1, Line 1
Expression result length exceeds the maximum. 8000 max, 11730 found.
Server: Msg 212, Level 16, State 1, Line 1
Expression result length exceeds the maximum. 8000 max, 11985 found.
Server: Msg 212, Level 16, State 1, Line 1
Expression result length exceeds the maximum. 8000 max, 12240 found.
Server: Msg 212, Level 16, State 1, Line 1
Expression result length exceeds the maximum. 8000 max, 12495 found.
Server: Msg 212, Level 16, State 1, Line 1
Expression result length exceeds the maximum. 8000 max, 12750 found.
Server: Msg 212, Level 16, State 1, Line 1
Expression result length exceeds the maximum. 8000 max, 13005 found.
Server: Msg 212, Level 16, State 1, Line 1
Expression result length exceeds the maximum. 8000 max, 13260 found.
Server: Msg 212, Level 16, State 1, Line 1
Expression result length exceeds the maximum. 8000 max, 13515 found.
Server: Msg 212, Level 16, State 1, Line 1
Expression result length exceeds the maximum. 8000 max, 13770 found.
Server: Msg 212, Level 16, State 1, Line 1
Expression result length exceeds the maximum. 8000 max, 14025 found.
Server: Msg 212, Level 16, State 1, Line 1
Expression result length exceeds the maximum. 8000 max, 14280 found.
Server: Msg 212, Level 16, State 1, Line 1
Expression result length exceeds the maximum. 8000 max, 14535 found.
Server: Msg 212, Level 16, State 1, Line 1
Expression result length exceeds the maximum. 8000 max, 14790 found.
Server: Msg 212, Level 16, State 1, Line 1
Expression result length exceeds the maximum. 8000 max, 15045 found.
Server: Msg 212, Level 16, State 1, Line 1
Expression result length exceeds the maximum. 8000 max, 15300 found.
Server: Msg 212, Level 16, State 1, Line 1
Expression result length exceeds the maximum. 8000 max, 15555 found.

Here's the code:

DECLARE @DataLength INT,
@bytes INT,
@i INT,
@start INT,
@sql1 VARCHAR(8000),
@sql2 VARCHAR(8000),
@sql3 VARCHAR(8000),
@sql4 VARCHAR(8000),
@sql5 VARCHAR(8000),
@table VARCHAR(100),
@field VARCHAR(100),
@where VARCHAR(100),
@holder VARCHAR(8000)

SET @table = 'NNEREN.nnerensql.dbo.idxmls1'
SET @field = 'medium_photo'
SET @where = 'id=430729'


--SET @sql = 'DECLARE @DataLength INT; SET @DataLength = (SELECT DATALENGTH(' + @field + ') as dataLength FROM ' + @table + ' WHERE ' + @where + ')'

--exec(@sql)


SET @DataLength = (SELECT DATALENGTH(medium_photo) as dataLength FROM NNEREN.nnerensql.dbo.idxmls1 WHERE id=430729)

SET @sql1 = 'SELECT '
SET @sql2 = ''
SET @sql3 = ''
SET @sql4 = ''
SET @sql5 = ''

SET @bytes = 0
SET @i = 0

WHILE @bytes < @DataLength
BEGIN
SET @start = @bytes

IF(@bytes > 0)
SET @holder = ','
ELSE
SET @holder = ''

SET @bytes = @bytes + 255
SET @i = @i + 1

SET @holder = @holder + ' SUBSTRING(' + @field + ',' + convert(varchar(5),@start) + ',' +
convert(varchar(5),@bytes) + ') as c' + CONVERT(varchar(5),@i)

IF (LEN(@sql1) + LEN(@holder) < 4000)
SET @sql1 = @sql1 + @holder

IF ((LEN(@sql2) + LEN(@holder) < 4000) AND (LEN(@sql1) + LEN(@holder) > 4000))
SET @sql2 = @sql2 + @holder

IF ((LEN(@sql3) + LEN(@holder)) < 4000) AND ((LEN(@sql1) + LEN(@sql2) + LEN(@holder) > 8000))
SET @sql3 = @sql3 + @holder

IF((LEN(@sql4) + LEN(@holder)) < 4000) AND ((LEN(@sql1) + LEN(@sql2) + LEN(@sql3) + LEN(@holder) > 12000))
SET @sql4 = @sql4 + @holder

IF ((LEN(@sql5) + LEN(@holder)) < 4000) AND ((LEN(@sql1) + LEN(@sql2) + LEN(@sql3) + LEN(@sql4) + LEN(@holder) > 16000))
SET @sql5 = @sql5 + @holder


END

print @sql1 + @sql2 + @sql3 + @sql4 + @sql5 + ' FROM ' + @table + ' WHERE ' + @where

exec(@sql1 + @sql2 + @sql3 + @sql4 + @sql5 + ' FROM ' + @table + ' WHERE ' + @where)

 

[AndyHopper]

Hmm, the sql query string is only 2500 characters or so. It all fits in one local variable. The problem is def in the rowsize limit of SQL. Is there any way to circumvent this though?

 

[Zathras]

See the post in thread183-490435

 

[AndyHopper]

I'm still completely stumped by this problem. It appears that the SUBSTRING function is outputted an error when it tries to access image data after the 8000 character mark? I don't understand why this behavior is happening, and I haven't been able to find any explaination. I can work with the first 8000 characters of the image field, without any issues.

Here's the query:
SELECT SUBSTRING(medium_photo,14280,14535) as c57, SUBSTRING(medium_photo,14535,14790) as c58,
SUBSTRING(medium_photo,14790,15045) as c59, SUBSTRING(medium_photo,15045,15300) as c60
FROM NNEREN.nnerensql.dbo.idxmls1 WHERE id=430729

And the result:
Server: Msg 212, Level 16, State 1, Line 1
Expression result length exceeds the maximum. 8000 max, 14535 found.
Server: Msg 212, Level 16, State 1, Line 1
Expression result length exceeds the maximum. 8000 max, 14790 found.
Server: Msg 212, Level 16, State 1, Line 1
Expression result length exceeds the maximum. 8000 max, 15045 found.
Server: Msg 212, Level 16, State 1, Line 1
Expression result length exceeds the maximum. 8000 max, 15300 found.

 

[AndyHopper]

Problem solved!

I as using the wrong syntax for the SUBSTRING function....

I was using SUBSTRING(string,start,end) but I should have been using SUBSTRING(string,start,length). Ooops!