CODEWITHSUNDEEP
sqlt-sqlvarchar
Lior Ohana
Lior Ohana

Reputation: 3527

String or binary data would be truncated SQL Error

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?

Upvotes: 9

Views: 68987

Answers (4)

Feroj Ahmed
Feroj Ahmed

Reputation: 11

You are passing a string bigger than the column in database.

Like your database table is as follow:

 EXECUTIVE  varchar(15)

But your insert string like (in vb.net):

cmd.Parameters.Add("@EXECUTIVE", Data.SqlDbType.VarChar, 150).Value = CmbExecutive.Text.ToString()

Upvotes: 1

Junior Mayhe
Junior Mayhe

Reputation: 16411

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

Upvotes: 1

Mark Kremers
Mark Kremers

Reputation: 1789

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....

Upvotes: 8

knittl
knittl

Reputation: 265956

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.

Upvotes: 8

Related Questions