|
|
Error Message
Server: Msg 131, Level 15, State 1, Line 1
The size (<Data Type Size>) given to the type 'varchar /
char / nvarchar / nchar / binary / varbinary' exceeds
the maximum allowed for any data type (8000).
Server: Msg 131, Level 15, State 1, Line 1
The size (<Data Type Size>) given to the column '<Column
Name>' exceeds the maximum allowed for any data type
(8000).
|
Causes
As the error message suggests, this error is generated when specifying the length or size of a local variable or column in a table that exceeds the maximum allowed size for that particular data type. This usually occurs when a local variable or table column is defined as VARCHAR, CHAR, NVARCHAR, NCHAR, VARBINARY, or BINARY.
The VARCHAR data type is a variable-length, non-Unicode character data while the CHAR data type is a fixed-length, non-Unicode character data with a length of n bytes, where n must be a value from 1 through 8,000. The NVARCHAR data type is a variable-length Unicode character data while the NCHAR data type is a fixed-length Unicode character data of n characters, where n must be a value from 1 through 4,000. The BINARY data type is a fixed-length binary data with a length of n bytes, where n is a value from 1 through 8,000 while the VARBINARY data type is a variable-length binary data of n bytes, where n must be a value between 1 and 8,000.
Here are a few examples on how this error can be encountered:
DECLARE @BookSummary NVARCHAR(10000)
Msg 131, Level 15, State 3, Line 1
The size (10000) given to the type 'nvarchar' exceeds the maximum allowed for any data type (8000).
DECLARE @ProductDescription VARCHAR(10000)
Msg 131, Level 15, State 3, Line 1
The size (10000) given to the type 'varchar' exceeds the maximum allowed for any data type (8000).
CREATE TABLE [dbo].[Books] (
[BookID] INT,
[BookTitle] VARCHAR(200),
[BookSummary] VARCHAR(10000)
)
Msg 131, Level 15, State 2, Line 4
The size (10000) given to the column 'BookSummary' exceeds the maximum allowed for any data
type (8000).
A typical case when this error may be encountered is when generating a dynamic SQL script and the length of the local variable used is more than 8000 characters in the case of a VARCHAR data type.
DECLARE @SQLScript VARCHAR(10000)
/* Build SQL Script Here */
EXECUTE ( @SQLScript )
Msg 131, Level 15, State 3, Line 1
The size (10000) given to the type 'varchar' exceeds the maximum allowed for any data type (8000).
Msg 137, Level 15, State 2, Line 7
Must declare the scalar variable "@SQLScript".
Solution / Work Around
To avoid this error, make sure that the length specified for a VARCHAR or CHAR data type does not exceed 8000 or in the case of NVARCHAR or NCHAR data type that it does not exceed 4000.
Alternatively, when using SQL Server 2005 and above, in the case of VARCHAR if the value to be stored in the local variable or table column will exceed 8000 characters, VARCHAR(MAX) should be used instead. In the case of NVARCHAR, if the value to be stored in the local variable or table column will exceed 4000 characters, then NVARCHAR(MAX) should be used instead.
The MAX specified expands the storage capabilities of the VARCHAR and NVARCHAR data types. VARCHAR(MAX) and NVARCHAR(MAX), together with VARBINARY(MAX), are collectively called large-value data types and these can store up to 2^31-1 bytes of data. VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX) data types should be used instead of TEXT, NTEXT and IMAGE data types, respectively.
Using the sample scripts above that generated the error, here’s the updated version that uses the MAX specifier to avoid this error message:
DECLARE @BookSummary NVARCHAR(MAX)
DECLARE @ProductDescription VARCHAR(MAX)
CREATE TABLE [dbo].[Books] (
[BookID] INT,
[BookTitle] VARCHAR(200),
[BookSummary] VARCHAR(MAX)
)
DECLARE @SQLScript VARCHAR(MAX)
/* Build SQL Script Here */
EXECUTE ( @SQLScript )
In the case of the executing a long script that exceeds 8000 characters and the version of SQL Server used is SQL Server 2000 where the MAX specifier does not exist, one way of overcome this error is with the use of multiple local variables and concatenate these local variables when it is being executed.
DECLARE @SQLScript1 VARCHAR(8000)
DECLARE @SQLScript2 VARCHAR(8000)
DECLARE @SQLScript3 VARCHAR(8000)
/* Build SQL Script Here */
EXECUTE ( @SQLScript1 + @SQLScript2 + @SQLScript3 )
|