Fox Pro and ODBC

wdudek · Post by **wdudek** » Thu Jul 01, 2004 4:01 pm

I am trying to extract from a fox pro table into Oracle, when I view the data in fox pro through the odbc connection in Datastage it looks ok. However when I run the job after extracting 35000 records it dies with the following error

Project:CFSPROD (datastage)
Job name:VfAssetExtract
Event #:710
Timestamp:7/1/2004 1:02:40 PM
Event type:Warning
User:DATASTAGE\u1250
Message:
VfAssetExtract..Transformer_6.DSLink7: DSD.BCIGetNext call to SQLFetch failed.
SQL statement:SELECT `asset`.`serial_num`, `asset`.`status_code`, `asset`.`asset_num`, `asset`.`contract`, `asset`.`legal`, `asset`.`inv_loc`, `asset`.`inv_code`, `asset`.`inv_method`, `asset`.`type`, `asset`.`mfg`, `asset`.`model`, `asset`.`description`, `asset`.`notes`, `asset`.`source_id`, `asset`.`customer_name`, `asset`.`orig_equip_cost`, `asset`.`orig_lease_date`, `asset`.`rental_pymt`, `asset`.`sec_deposit`, `asset`.`date_returned`, `asset`.`meter_rdg`, `asset`.`orig_resid`, `asset`.`booked_resid`, `asset`.`asking_price`, `asset`.`damage_billed`, `asset`.`damage_collected`, `asset`.`ti_invcnum`, `asset`.`ti_invcamt`, `asset`.`ti_invcdate`, `asset`.`ti_paidamt`, `asset`.`ti_paiddate`, `asset`.`ti_checkno`, `asset`.`ti_saleprice`, `asset`.`ti_funding`, `asset`.`ti_reason`, `asset`.`ti_subvar`, `asset`.`ti_damaged`, `asset`.`reseller`, `asset`.`resellto`, `asset`.`dest_id`, `asset`.`invoice_num`, `asset`.`invoice_date`, `asset`.`invoice_due_date`, `asset`.`invoice_inits`, `asset`.`last_pay_date`, `asset`.`last_pay_amt`, `asset`.`tot_pay_amt`, `asset`.`check_number`, `asset`.`total_check`, `asset`.`misc_amt`, `asset`.`misc_date`, `asset`.`misc_check`, `asset`.`misc_reason`, `asset`.`proceeds`, `asset`.`costs`, `asset`.`adjustment`, `asset`.`gain`, `asset`.`cs_entry`, `asset`.`months`, `asset`.`residpct`, `asset`.`basispct`, `asset`.`reducedpct`, `asset`.`basis`, `asset`.`commgain`, `asset`.`commission`, `asset`.`flag`, `asset`.`begindate`, `asset`.`enddate`, `asset`.`beginning`, `asset`.`returnseol`, `asset`.`returnsother`, `asset`.`sales`, `asset`.`gains`, `asset`.`writeoffs`, `asset`.`adjustments`, `asset`.`ending`, `asset`.`suppress`, `asset`.`pickedup`, `asset`.`pickedupby`, `asset`.`missingserial`, `asset`.`condition`, `asset`.`flagcondition`, `asset`.`flagcopycount`, `asset`.`flagoptions`, `asset`.`consigned`, `asset`.`cust_address1`, `asset`.`cust_address2`, `asset`.`cust_address3`, `asset`.`cust_city`, `asset`.`cust_state`, `asset`.`cust_zip`, `asset`.`writedown` FROM `asset`
SQLSTATE=22003, DBMS.CODE=0
[DataStage][SQL Client][ODBC][Microsoft][ODBC Visual FoxPro Driver]Error: Column 60 ( residpct )

I looked the sqlstate code up on this site and only saw references to stored procedures, does anyone know if that somehow relates to fox pro and the way odbc treats it? I'm going to try dumping the data to a text file to see what lies after the 35000 record mark, because I'm pretty sure that this is where the job has failed every time I ran it.

Any help or advice on extracting from fox pro would be a big help.
Thanks,
Bill

ray.wurlod · Post by **ray.wurlod** » Thu Jul 01, 2004 4:10 pm

My guess is that there's something awry with the data in the column `asset`.`residpct` in that row.

Run the job in debugger; set a breakpoint on the input link and edit its expression to @INROWNUM >= 34998. Execution of the job will pause at row number 34999, and you can then step through at human speed.

Link the FoxPro table to a spreadsheet, and look at row number 35000 to see what you can see there. (This is easier than a text file.)

If you do dump the table to a text file, use a viewer that can display line numbers!

wdudek · Post by **wdudek** » Fri Jul 02, 2004 8:17 am

Looks like my problem isn't specific to datastage, I tried to extract the data through an odbc connection to excel and it failed with a similiar error referencing the same column. Thanks for the suggestions, but it looks like I'm gonna have to get the guy who wrote the program using hte fox pro table to look at it.