Post reply

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

  • 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

  • August 4, 2010 at 3:49 am

    #1201947

    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]

  • August 4, 2010 at 3:59 am

    #1201952

    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.

  • August 5, 2010 at 3:46 am

    #1202595

    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?

  • August 5, 2010 at 4:17 am

    #1202602

    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

  • August 6, 2010 at 11:51 am

    #1203718

    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

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

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