Best practice: Syntax error converting value -> determine problematic row

Question

Best practice: Syntax error converting value -> determine problematic row

Thom-

SSC Rookie

Points: 42
More actions
August 4, 2010 at 3:28 am

#236146

Hello together,
when I'm importing data from textfiles I often have the problem with some rows of the select, which can not be converted correctly.
I don't know how to find out best which one of the for example 10,000 rows is the one causing my problems.
Sometimes I can search for the row via "isnumeric", but mostly I don't even know which column I have to check.
Sometimes I create a cursor with the same variables as my target table has - then i try the inserts row by row and print every time the key of the select. But this method does only work when my select statement has a key. This method is also very time expensive when you have tables with many columns.
Is there any better way to determine the row/column, which is causing problems?
Greetings
-Thomas

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

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

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

Are you using SSIS? It can help you to filter the problem rows out.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Fredy James Old Hand Points: 387 More actions · Answer 2

If SSIS pacckage is not an option, then you can try using the BULK INSERT with a format file to import the text file.

If the incoming text file columns are not know then you basically have to import the text file into a temp table for each row and validate columns using clever dynamic sql queries.

Thom- SSC Rookie Points: 42 More actions · Answer 3

Well, I just have a simple query in my query window ->

insert into testtable

select a,b,c,d from openquery(server2,'select a,b,c,d from testtable2)

Now I'm getting the error -> how would I find out where the error is with the SSIS solution?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Thom- (8/5/2010)
Well, I just have a simple query in my query window ->
insert into testtable
select a,b,c,d from openquery(server2,'select a,b,c,d from testtable2)
Now I'm getting the error -> how would I find out where the error is with the SSIS solution?

Adjust the datatype of your target table columns to best fit the source data. Problems with date-type data in the data source can often be solved by changing the datatype of the target table column to a suitable character type. Once you've captured all of your source rows, it's no big deal to identify and correct bad data.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

tfifield SSCrazy Eights Points: 9655 More actions · Answer 5

I'm not a big OpenQuery or OpenRowSet guy and I haven't used SSIS all that much. I have, however, done a fair amount of BULK INSERT type code from CSV and Text files. Bulk Insert always gave me the row number where an error occurred.

Todd Fifield