Find the error column and the row

Question

Post reply

Find the error column and the row

demonfox

SSCertifiable

Points: 6289
More actions
June 4, 2013 at 12:58 am

#294671

Hi All,
I am facing a problem with finding the exact error row and column where the error occured.
Scenario :
1. TableA has 300+ columns all Varchar datatype
2. TableB has exact number of columns as da TableA but different datatypes
TableB is to be populated from the TableA .
While including a single insert statement, converting accordingly, it error out with DataConversion error. I am having a trouble finding the exact record and column for the error description.
I have figure out a way to do that , i.e. Adding an Identity column in the TableB ; after rollback, Identity would still be bound to the table. So, the next insert would give me the point of failure.
But, Is there any way to do this without altering table ?
here is sample code to work it out .
create table varchartest(a varchar(30),b varchar(30))
create table varchartest_1(a varchar(30),b int)
insert into varchartest values ('xyz','1')
,('abc','2')
,('bcd','3')
,('efg','4')
,('erer','5')
,('rerer','x')
,('rerer','7')
-- Edit
insert into varchartest_1(a,b) select a,b from varchartest -- dataconversion error
-- solution to find out the exact row
-- alter table varchartest_1 add c int identity
-- insert into varchartest_1(a,b) select top 1 a,b from varchartest
select * from varchartest_1
Thanks
~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

sumadevu Old Hand Points: 365 More actions · Answer 1

create table varchartest(a varchar(30),b varchar(30))

create table varchartest_1(a varchar(30),b int)

insert into varchartest values ('xyz','1')

,('abc','2')

,('bcd','3')

,('efg','4')

,('erer','5')

--,('rerer','x')

,('rerer','7')

alter table varchartest_1 add c int identity

insert into varchartest_1(a,b) select a,b from varchartest

select * from varchartest_1

The above will be executed without error. In the example provided, the erroneous line was ('rerer','x') since the column b contains a varchar value which cannot be inserted to the int value of table varchartest_1 column b.

The query can be modified to include creating the identity column along with the table creation itself. So the query will look like

create table varchartest(a varchar(30),b varchar(30))

create table varchartest_1(a varchar(30),b int,c int identity(1,1))

insert into varchartest values ('xyz','1')

,('abc','2')

,('bcd','3')

,('efg','4')

,('erer','5')

--,('rerer','x')

,('rerer','7')

--alter table #varchartest_1 add c int identity

insert into varchartest_1(a,b) select a,b from varchartest

select * from varchartest_1

demonfox SSCertifiable Points: 6289 More actions · Answer 2

Thanks for the reply , I have modified the original post to uncomment few lines, which actually throws error ..

I am aware of identity column usage to find the row . I was looking for any other way possible without altering the table.

I guess I could use the temp table for that; avoiding any ddl changes to the existing table.

please let me know in case of any other solution.. I will post it for the same as soon as I find anything.

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:

HowardW One Orange Chip Points: 29109 More actions · Answer 3

The insert order isn't guaranteed to be the same between two inserts, so the identity trick may not work. Also, I suspect it may allocate identities before the error is thrown, depending on the plan, so it may not tally up with the exact point the error occurred.

You can drive yourself insane looking for conversion errors in SQL Server, the error message is woefully inadequate.

If I don't have a hunch where the problem is and am not able to find it quickly, I'd tend to revert to one of the following:

1) Binary searching for the error - keep splitting the source table in half, then keep splitting the side that gives you the error until you arrive at a number of rows where you can see the error (this takes a lot less time than you'd think).

2) Fire up SSIS and set up a proper data flow task and redirect error rows to another output so you can spot them.

demonfox SSCertifiable Points: 6289 More actions · Answer 4

HowardW (6/4/2013)
The insert order isn't guaranteed to be the same between two inserts, so the identity trick may not work. Also, I suspect it may allocate identities before the error is thrown, depending on the plan, so it may not tally up with the exact point the error occurred.
You can drive yourself insane looking for conversion errors in SQL Server, the error message is woefully inadequate.
If I don't have a hunch where the problem is and am not able to find it quickly, I'd tend to revert to one of the following:
1) Binary searching for the error - keep splitting the source table in half, then keep splitting the side that gives you the error until you arrive at a number of rows where you can see the error (this takes a lot less time than you'd think).
2) Fire up SSIS and set up a proper data flow task and redirect error rows to another output so you can spot them.

Yes, I tried for Newton Raphsson method, i.e. binay search .. It's the proper way to find out the correct resultset ; and SSIS is even better ..

As for binary search it's too long a method for millions of records. I will search it out , how does it behave ?

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:

HowardW One Orange Chip Points: 29109 More actions · Answer 5

demonfox (6/4/2013)
Yes, I tried for Newton Raphsson method, i.e. binay search .. It's the proper way to find out the correct resultset ; and SSIS is even better ..
As for binary search it's too long a method for millions of records. I will search it out , how does it behave ?

My maths education didn't go past college (A-Level), but I've no idea how that method could possibly be applied to this?

Binary search will take between 20-25 attempts on millions of rows, assuming there's only one error row. Bear in mind that the last few will be on a handful of rows where you'll likely be able to spot the issue before then anyway.

SSIS is the proper way to do this when you don't trust the data types are compatible between source and target, especially if this is a process you'll be repeating.

Anshuman Saini Old Hand Points: 337 More actions · Answer 6

create table varchartest(a varchar(30),b varchar(30))

create table varchartest_1(a varchar(30),b int)

insert into varchartest

--values

select 'xyz','1'

union all

select 'abc','2'

union all

select 'bcd','3'

union all

select 'efg','4'

union all

select 'erer','5'

--union all

--select 'rerer','x'

union all

select'rerer','7'

-- Edit

insert into varchartest_1(a,b) select a,b from varchartest -- dataconversion error

-- solution to find out the exact row

-- alter table varchartest_1 add c int identity

-- insert into varchartest_1(a,b) select top 1 a,b from varchartest

select * from varchartest_1

use this query

insert into varchartest_1(a,b) select a,cast (b as int) from varchartest