FIX : ERROR Msg 244, Level 16, State 1, Line 1
FIX : ERROR Msg 245, Level 16, State 1, Line 1
This error can happen due to conversion of one data type to incompatible datatype.
Few examples are:
VARCHAR to INT, INT to TINYINT etc.
I have spotted this error happening with CAST or ISNULL, please add comments if you have come across this error in other examples. Following scripts will create this error.
SELECT CAST('111111' AS SMALLINT);
SELECT CAST('This is not smallint' AS SMALLINT);
The errors received from above two scripts are :
Msg 244, Level 16, State 2, Line 1
The conversion of the varchar value ‘111111’ overflowed an INT2 column. Use a larger integer column.
Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the varchar value ‘This is not smallint’ to data type smallint.
Interesting detail about ISNULL(@var1, @var2) is that it always cast @var2 to @var1. ISNULL function can generate the same error demonstrated in following script.
DECLARE @MyVal TINYINT
SELECT ISNULL(@MyVal,'123456') AS MyVal
The error received from above script is :
Msg 245, Level 16, State 1, Line 3
Conversion failed when converting the varchar value ‘LongString’ to data type tinyint.
Fix/Solution/WorkAround:
Use CAST to convert to correct data type – change the data or change the data type.
Examples using workaround:
SELECT CAST('111111' AS VARCHAR(6));
SELECT CAST('111' AS SMALLINT);
DECLARE @MyVal TINYINT
SELECT ISNULL(@MyVal,'123') AS MyVal
Reference : Pinal Dave (https://blog.sqlauthority.com)
9 Comments. Leave new
i am have this, when am returning a string from a procedure to a varchar variable.
alter procedure ani_pr_LoginCostomerReturn @Log_Id varchar(8) as
declare @name varchar(20)
select @name = C.Cust_Name from ani_Login L inner join ani_BCustomer C on C.Cust_Id=L.Cust_Id where Log_Id=@Log_Id
return @name
declare @name varchar(20)
set exec @name=ani_pr_LoginCostomerReturn ‘login3’
print @name
this s wat i have the procedure.
thanks for helping me in finding the solution.
The RETURN statement can only return an integer expression. You must use an output paramerer to retrun an other type.
alter procedure ani_pr_LoginCostomerReturn @Log_Id varchar(8) , @name varchar(20) OUTPUT as
select @name = C.Cust_Name from ani_Login L inner join ani_BCustomer C on C.Cust_Id=L.Cust_Id where Log_Id=@Log_Id
declare @name varchar(20)
exec ani_pr_LoginCostomerReturn ‘login3’,@name OUTPUT
print @name
This script must solve your problem
I just had this issue crop up on my PayPal IPN stored procedure I created several years ago. what I did was create a table that captures the 40 form variables that paypal sends in its instant payment notification post.
Because only about 5 of the values are known to be of a particular type, I type most of the columns in the sql table as varchar(255). And this seemed to do the trick just fine until one of my customer’s invoice numbers jumped to 15 digits in length. Then I got this error:
[Macromedia][SQLServer JDBC Driver][SQLServer]The conversion of the varchar value ‘242062181872839’ overflowed an int column. Maximum integer value exceeded.
For this client we tried to CAST the value but it didn’t work so we opted to change the column to numeric(18,0)
In my procedure with 2 parameters (one varchar(150) and second tinyint) , I have only a Delete query based on the values of parameters , something like :
P_myProc @param1 Varchar(150) , @param2 tinyint
Delete myTable
Where ((@param2 = 1) And @Param1 = myTable.Col1)
Or ((@param2 = 2) And @Param1 = myTable.Col2)
When executing it like :
Exec P_MyProc @param1 = ‘3232_form1’ , @param2 = 0
it gives me the strange error :
Conversion failed when converting the varchar value ‘3232_form1’ to data type tinyint.
Where is conversion happening here?
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value ‘} ‘ to data type tinyint.
hi , i am having this error when i try to do this,
select ‘{‘ +servicecode+ ‘} ‘ as data from emooutward where superid ”
my service code column in emooutward table is tiny int,please guide me how to cast and one more thing this my first experience to discuss any forum and i am beginner so encourage me
Hi,
I am having this error with the following query:
SELECT @MaxNumber = MAX(DeletedDate) FROM DamageCause
I am asking first if all the values in that column(“DeletedDate”) are numeric, if it is the case I ask if at least one is decimal if it is not then I try to determine which is the minimum integer type that could recommend, but, when I put all values in that column as bigints it gives me the following error:
The conversion of the varchar value ‘3147483648’ overflowed an int column.
BTW I like your site, very instructive
The conversion of the varchar value ‘256’ overflowed an INT1 column. Use a larger integer column…
when i put the credit in shop it gave me error like this……
please tell me how to solve this……….
Hi,
Previously it was given as int in table while creating, and in SP it is taken as nvarchar(300). Problem is there, and i solved it now. My question is now about mobile number validations such as +918788887888888. And the number should not be more than 10 digits(excluding +91). Kindly suggest me on this.
but accept char more than 100.
error:
Re: Conversion failed when converting the nvarchar value 918788887888888 to data type int colum
Regrads,
Babulal
This error appears when trying to join two columns with the data types Varchar and int. to fix the error we need to type cast int to varchar