SQL Server Error : 131, Severity: 15. The size (%d) given to the %S_MSG ‘%.*ls

SQL Server Error : 131 Details

SQL Server Error: 131
Severity: 15
Event Logged or not: No
Description:
The size (%d) given to the %S_MSG ‘%.*ls’ exceeds the maximum allowed for any data type (%d).
Severity 15 Description:
Indicates syntax errors in the Transact-SQL command.

Error Description:

Server: Msg 131, Level 15, State 3, Line 1 The size () given to the type ‘varchar’ exceeds the maximum allowed for any data type (8000)

When the the length is specified in declaring a VARCHAR variable or column, the maximum length allowed is still 8000.

On a Microsoft SQL Server database when we get the error “String or binary data would be truncated” at some point its to do with size of Varchar datatype.

For Example : This error message is produced by a table with a VARCHAR(1000) column and you need to enter more than 1000 characters after diving into the T-SQL code.

The VARCHAR(Max), NVARCHAR(max), and VARBINARY(max) string data types were added in SQL Server 2005 to replace the TEXT, NTEXT, and IMAGE large object (LOB) data types. Except for NVARCHAR(max), which can only store 1GB of Unicode characters, all of these data types can store up to 2GB of data. As you might expect, these data types have the same maximum storage capacity as the ones being replaced. If you need more information on the differences between VARCHAR and NVARCHAR data types, I recommend reading the SQL Server differences between char, nchar, varchar, and nvarchar data types tutorial.

Exceeds the maximum allowed for any data type (8000)  or how to store more than 8000 characters in sql server ?

Why can’t we declare a column or variable of type VARCHAR(10000) if the VARCHAR(max) data type can store up to 2GB? This is a typical question among novices. Why are we only allowed to declare either a VARCHAR(8000) or a VARCHAR(max)? Because VARCHAR(n) is designed to store data in the row page, this is the case. Because the page header takes up the first 96 bytes of each data page, leaving 8,096 bytes for data, row overhead, and row offsets, you can’t specify a value larger than VARCHAR (8000).

The VARCHAR(Max), NVARCHAR(max), and VARBINARY(max) string data types were added in SQL Server 2005 to replace the TEXT, NTEXT, and IMAGE large object (LOB) data types. Except for NVARCHAR(max), which can only store 1GB of Unicode characters, all of these data types can store up to 2GB of data.

Solution for Resolving the Error

When we get this error SQL Server 131 or related like “exceeds the maximum allowed for any data type (8000)” then For SQL Server Error 131 we can use either VARCHAR(8000) or VARCHAR(MAX) .

Note that for SQL Server Error 131, VARCHAR(MAX) of SQL Server 2005 is replacement of datatype TEXT of SQL Server 2000.

SQL Server Error Code and solution summary

SQL Server Error: 131
Severity: 15
Event Logged or not: No
Description:
The size (%d) given to the %S_MSG ‘%.*ls’ exceeds the maximum allowed for any data type (%d).

for SQL Server Error 131, VARCHAR(MAX) of SQL Server 2005 is replacement of datatype TEXT of SQL Server 2000.

For SQL Server Error 131 we can use either VARCHAR(8000) or VARCHAR(MAX)  if you are sure its long char values are to be stored.