That does not change the inconsistant behavior. I could not see that constraint documented anywhere, but it did not prevent the connection so I assume it was accepted. However, the returned results are still variant. It seems the number of returned records is the same from ODBC, but the record contents are different.
>Troy,
>
>Make sure that you have 'BackgroundFetch=No' in the connection string and try again.
>
>>I am running this query against my local table thru ODBC and getting different results each time. The behavior is consistant when not going thru ODBC or when using ODBC against the same table in Access, Pervasive, or SQL Server.
>>
>>This is very scary! I always assumed I could trust VFP data even when talking thru ODBC.
>>
>>What Gives!????
>>
>>
>>clear all
>>close all
>>release all
>>private pDate
>>pDate={'02/11/2004 12:00:00 AM'}
>>SET ENGINEBEHAVIOR 70
>>lcSql = [select acct_bal.*, cases.pk as cases_pk, ] +;
>> [trustee_no.pk as trustee_no_pk, trustee_no.tid as trustee_no_tid ] +;
>> [from Acct_Bal ] +;
>> [left join cases on cases.case_no = acct_bal.case_no ] +;
>> [left join trustee_no on trustee_no.tid = acct_bal.tid ] +;
>> [where acct_bal.bal_date = ?pDate ] +;
>> [having ISNULL(cases_pk) ]
>>ch=sqlstringconnect(tcConnectionString)
>>=sqlexec(ch,lcSql,'curTest')
>>?RECCOUNT('curTest')
>>=SQLDISCONNECT(ch)
>>select * from curTest into cursor curTest2 group by tid, case_no into cursor curTest2
>>?RECCOUNT('curTest2')
>>
>>
>>Side question: I originally had the having ISNUll(cases_pk) as part of the where (where ... and cases_pk is null) which VFP liked natively (and SQL ODBC), but does not like this syntax going thru ODBC to VFP. Why is the VFP syntax requirements different when going thru ODBC then when running natively?
>>
>>Troy