>>>I have 4 SQL Selects I want to run against an Oracle back end. 2 of them work fine, the other 2 do not. The SQL was designed by an Oracle DBA since I was unfamiliar with the databases. All 4 scripts run fine when I issue them using Oracle Enterprise Mgr SQL Worksheet, but only 2 of them work when I execute from VFP. I'm using VFP 6.0 Service Pack 3. The following is the code:
>>>
>>>gnConnHandle = SQLCONNECT('SWORD', 'userid', 'pwd')
>>>=SQLSETPROP(gnConnHandle, 'asynchronous', .T.)
>>>=SQLEXEC(gnConnHandle, lcSQLString, 'Results')
>>>
>>>Where lcSQLString is the SQL script mentioned above. I'm storing the SQL in a Memo field in a table, since I have 4 scripts to run now that may change later.
>>>
>>>I took one of the scripts that doesn't work and added an additional and statement in the where clause to limit the number of records. When I do that, it works. So I'm guessing that maybe its just timing out. Any thoughts?
>>
>>Can you post those two SQL here?
>
>Sure:
>
>select distinct max(c.user_contr_num) user_contr_num,
> v.inv_id,
> l.contr_num,
> v.project_id,
> v.season_code,
> v.unit_num,
> v.element,
> u.unit_type_code,
> v.oeb_code
>from p_inventory v, p_project_unit u, p_contract_lead l, p_contract c
>where v.project_id = u.project_id
>and v.inv_id = l.lead_id
>and c.contr_num = l.contr_num
>group by v.inv_id,
> l.contr_num,
> v.project_id,
> v.season_code,
> v.unit_num,
> v.element,
> u.unit_type_code,
> v.oeb_code
>
>This one works fine in SQL Worksheet, but returns nothing when issued in a SQLEXEC() call from VFP.
>
>And the other:
>
>select distinct max(c.user_contr_num) user_contr_num,
> r.property_id,
> r.resv_num,
> r.contr_num,
> r.date_checked_in,
> r.date_checked_out,
> r.resv_status_code,
> r.resv_type_code,
> s.pm_unit_type_id,
> m.pm_unit_type
>from p_reservation r, p_resv_unit s, p_resv_guest g, p_pm_unit_type m,
>p_contract_lead l, p_contract c
>where r.resv_num = s.resv_num
>and r.resv_num = g.resv_num
>and r.property_id = m.property_id
>and g.lead_id = l.lead_id
>and c.contr_num = l.contr_num
>group by r.property_id,
> r.resv_num,
> r.contr_num,
> r.date_checked_in,
> r.date_checked_out,
> r.resv_status_code,
> r.resv_type_code,
> s.pm_unit_type_id,
> m.pm_unit_type
>
>This one also works in SQL Worksheet, but not when issued via SQLEXEC() from VFP.

I can't see anything wrong in the SQL statement. Its weird. Who is the Vendor of the Oracle ODBC Driver you are using? Microsoft or Oracle Corporation?

Try running it using each one and see what happens.

Bye
Jayesh