String or binary data would be truncated SQL Error

Question

I have a SQL stored procedure that accepts a parameter of type VARCHAR(MAX). As far as I know and according to all I read about, the max size for such strings is 2GB: MSDN

For some reason when passing a string larger than 8KB I get:

String or binary data would be truncated.

Why do I get this error message, and how can I resolve it?

Answer 1

According to BoL (the link you specified) there is a difference in interpretation. The maximum amount you can use in a query (the n part) is 8000. For storage purposes the varchar(max) can handle 2GB on disk.

It is just interpretation of datatypes for querying and storage purposes. So bottom line, you can only use 8000 chars in a query....

Answer 2

to avoid this problem, you have to cast your string first to varchar(max):

column = cast(other_column as varchar(max))

this way any string longer than max (8000 or 4000, depending on version) will be truncated to the maximum length.

Answer 3

You are passing a string bigger than the column in database, right? Try to increase the size of your column.