none
fOXPRO Table Import RRS feed

  • Question

  • I am Having Data in Foxpro Tables, which i want to import into SQL tables, for this i created linked server using vfpoledb provider and i am running a query ( given at the bottom)

    is it possible to know the error causing ROW number when sql throws error like

    OLE DB provider "VFPOLEDB" for linked server "japtech" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
    Msg 7341, Level 16, State 2, Line 2
    Cannot get the current row value of column "[japtech]...[sotr0210].amount" from OLE DB provider "VFPOLEDB" for linked server "japtech". The provider cannot determine the value for this column.

    so that i can directly see what is wrong in the data being imported,

    as old FoxPro tables are quite old , they are having lot of data it is difficult to get the real error causing rows,

    normally the errors are due to truncating, decimal fields improperly formatted in foxpro tables (suppose in a filed whose width is 5 and decimal places is 2 i see some values like 9999.0in the table , here i can increase the foxpro table to 6,2 and solve the problem ,for that matter i want the ROW Number where unaccepted values has caused SQL not to accept it)

    delete oaitem
    insert into oaitem (
    oa_ui, srl, begindt, enddt, item_ui, po_entryno, po_over, po_type, ac_code,
    item_no,qty, rate,
    balance_qty, po_amount, po_no, po_dt, dl, amount,mtrl_rate,
     po_srno, ss, rl, buyer_name, buyer_part_no,
    addondie, add_cst1,
    --amrt_hdng, bom_item,
    donm_pp, dond_pp,
    --mtrl_hdng,
    ex_chptrno,packingopt,xise_hdopt,xise_head
    ,Ecess_head, hec_head
    ,packing_rt, misc,
    td_rt,td_type,uom

    )
    select
    oa.ui, RIGHT( a.po_entryno,2) SRL, oa.po_dt BEGINDT,
    DATEADD( YEAR, 1000, oa.po_dt) ENDDT, item.ui, a.po_entryno , convert( bit,a.po_over), a.po_type,a.ac_code,
    a.item_no,a.qty, a.rate,
    a.balance_q, a.po_amount, a.po_no, a.po_dt,LTRIM(RTRIM(a.delivery)),a.amount, a.mtrl_rate,
      a.po_srno, a.ss, a.rl, a.buyer_name, a.buyer_pno,
     a.addondie, a.add_cst1,
     --a.amrt_hdng, a.bom_item,
     a.mtrl_rate,a.die_amrtrt,
     --a.mtrl_hdng,
    LTRIM(RTRIM(a.ex_chptrno)),
     a.packingopt,
    a.xise_hdopt,
    case when ltrim(rtrim(a.xise_hd)) = '' then null else convert(int,a.xise_hd) end,
    convert(int,a.cess_hd), convert(int,a.hec_hd)
    ,a.packingopt, a.misc,
    a.td_rt, a.td_type,a.uom

    from japtech...sotr0210 a inner join oa on a.po_type = oa.po_type and left( a.po_entryno,6) = oa.po_entryno
                     inner join ITEM on a.item_no = ITEM.item_no

    Friday, August 6, 2010 6:31 AM

All replies

  • You can process only the Select-Statement (with order by). You will get an output until the error occurs. The next row will be the eval.

    This is the way, I tested some errors.

    Other way:

    Use SSIS and redirect the row in case of errors to a texttfile.

    Friday, August 6, 2010 6:42 AM
  • You can process only the Select-Statement (with order by). You will get an output until the error occurs. The next row will be the eval.

    This is the way, I tested some errors.

    Other way:

    Use SSIS and redirect the row in case of errors to a texttfile.

    select statement does not give any error, it shows all the rows.

    i am was interested in knowing, is their any options in sql 2008 SSIS to get row number pertaining to given situation, as insert process is running from seelct and it throws error, it must have reached the row which it is not ready to accept , just give me that row number & column.

    Friday, August 6, 2010 2:53 PM
  • i am was interested in knowing, is their any options in sql 2008 SSIS to get row number pertaining to given situation, as insert process is running from seelct and it throws error, it must have reached the row which it is not ready to accept , just give me that row number & column.

    You need to post your question at the SSIS forum:

    SQL Server Integration Services

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Friday, August 13, 2010 12:57 PM
    Moderator