|
|
Error Message :
Server: Msg 248, Level 16, State 1, Line 4
The conversion of the varchar value overflowed an int
column. Maximum integer value exceeded.
Causes :
As the message suggests, you are trying to convert a varchar value using either the CAST function or the CONVERT function into an integer data type but the varchar value is greater than the maximum value that an integer data type can handle or less than the minimum value that an integer data type can handle. An integer data type is a whole number data that can hold a value from -2^31 (-2,147,483,648) through 2^31 – 1 (2,147,483,647).
|
To illustrate, the following SELECT statements will generate the error message:
SELECT CAST('3000000000' AS INT)
SELECT CAST('-3000000000' AS INT)
Server: Msg 248, Level 16, State 1, Line 2
The conversion of the varchar value '3000000000' overflowed an int column.
Maximum integer value exceeded.
sServer: Msg 248, Level 16, State 1, Line 3
The conversion of the varchar value '-3000000000' overflowed an int column.
Maximum integer value exceeded.
Another way that the error can be encountered is when changing the data type of an existing VARCHAR column in a table using the ALTER TABLE command and the column contains a value that cannot be converted into an integer data type.
To illustrate, let’s assume that you have the following table definition wherein the data came from a text file and during the DTS import, the first column which should have been an integer was declared as a varchar data type:
CREATE TABLE [dbo].[Transactions] (
[TransactionID] VARCHAR(20),
[TransactionType] VARCHAR(50)
)
After the import, you want to convert the TransactionID column from VARCHAR into an integer data type but you have a value of '3000000000' in one of your records. Issuing the following ALTER TABLE command will also generate the same error above:
ALTER TABLE [dbo].[Transactions]
ALTER COLUMN [TransactionID] INT
Server: Msg 248, Level 16, State 1, Line 1
The conversion of the varchar value '3000000000' overflowed an int column.
Maximum integer value exceeded.
The statement has been terminated.
Solution / Workaround:
To avoid this error, if you have a value in your VARCHAR column that is not within the range of an integer data type, convert the value into BIGINT, as follows:
SELECT CAST('3000000000' AS BIGINT)
SELECT CAST('-3000000000' AS BIGINT)
Similarly, when changing the data type of an existing VARCHAR column that has values not within an integer data type range, use BIGINT for that column:
ALTER TABLE [dbo].[Transactions]
ALTER COLUMN [TransactionID] BIGINT
A bigint data type is an integer (whole number) data that can hold a value from -2^63 (-9,223,372,036,854,775,808) through 2^63 – 1 (9,223,372,036,854,775,807).
|