Varchar(max) length

Question

Varchar(max) length

PRAMANA.DBA

SSCertifiable

Points: 5507
More actions
November 4, 2014 at 12:44 am

#291640

Hi,
In my sp, i have declared varchar(max), but it taking max 24600 length after that it throwing Binary data would be truncated error message.
What are the possible reasons,
As per my knowledge varchar(max) should allow 2147483647 length.
Thanks,
Ramana.

Viewing 7 posts - 1 through 6 (of 6 total)

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

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 1

It's hard to say without taking a look at the actual code.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 2

Quick question, any functions or operators used in the code? Many do have 8K limits. Also look out for implicit casts.

😎

PRAMANA.DBA SSCertifiable Points: 5507 More actions · Answer 3

Based on conditions i am concatenating string to that variable

i.e.

SET @QRY = 'select EMP_ID AS SITE_ID, EMP_FIRST_NAME AS SITE_NAME from EMPLOYEE where EMP_ID in ( '+@agentsSelectMany+')'

and also not using any functions.

@QRY length is varchar(max)

@agentsSelectMany length also varchar(max)

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 4

You can also use the debugger functionality of SSMS to step through the stored procedure to see on which line it exactly fails.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

PRAMANA.DBA SSCertifiable Points: 5507 More actions · Answer 5

Koen Verbeeck (11/4/2014)
You can also use the debugger functionality of SSMS to step through the stored procedure to see on which line it exactly fails.

I did that one also, found the issue, due to sting max length , If i take 25000 sting length "IN" clause then it is working, or else it throwing binary data would be truncated error.

Sean Pearce SSCoach Points: 15999 More actions · Answer 6

We really need to see your code to help you. As you can see below, the documented max values for VARCHAR(MAX) are correct. There is something else at fault here.

DECLARE @s-2 VARCHAR(MAX);

SET @s-2 = REPLICATE('X', 2147483647);

SET @s-2 = REPLICATE('X', 2147483648);

The SQL Guy @ blogspot[/url]

@SeanPearceSQL

About Me[/url]