Home converting the nvarchar value '2.17E+16' to data type int and The conversion of the nvarchar value '21700000001382594' overflowed an int column
Reply: 1

converting the nvarchar value '2.17E+16' to data type int and The conversion of the nvarchar value '21700000001382594' overflowed an int column

Mike Eale
1#
Mike Eale Published in 2018-01-11 12:06:18Z

I have issues deleting data I created the following using TSQL:

CREATE TABLE [dbo].[google] (
[ad] [varchar](255) NULL,
[ad_id] [varchar](30) NULL ) ON [PRIMARY]
GO

CREATE TABLE [dbo].[gmap] (
[ad] [nvarchar](200) NULL,
[ad_id] [nvarchar](200) NULL) ON [PRIMARY]
GO

create view [dbo].[v_google] as 
select a.[ad], a.[ad_id]
from [dbo].[google] a
left join [dbo].[gmap] b on a.[ad_id] = b.[ad_id]
GO

I inserted the data into the tables. When I tried to delete data with TSQL below:

delete b 
from [dbo].[v_google] a
join [dbo].[google] b on a.[ad] = b.[ad]
where a.ad is null

I get error message:

Msg 245, Level 16, State 1, Line 45
Conversion failed when converting the nvarchar value '2.17E+16' to data type int.
Msg 248, Level 16, State 1, Line 45
The conversion of the nvarchar value '21700000001382594' overflowed an int column.

I've looked at the solutions regarding the error message but the TSQL used are too advance for me and didn't understand it, since I'm a new learner in SQL SERVER.

JeromeE
2#
JeromeE Reply to 2018-01-11 12:50:47Z

You probably have an implicit cast into INT in one of the members of the ON clause of the join. I would try to change the ON clause by casting the two members as NVARCHAR. Like this (I assume NVARCHAR(255) is long enough, you can try also BIGINT it you only have large numbers, it will make the query run faster):

join [dbo].[google] b on CAST (a.[ad] AS NVARCHAR(255)) = CAST (b.[ad] AS NVARCHAR(255))

SQL server makes implicit casts into INT if there are only integers in the data set. Then when SQL server compare the two columns in JOIN, it tries to cast the two sides of the ON in the same type (in that case INT). That's why you have a convert to INT error even if you don't explicitly try to convert to INT.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.297597 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO