SDAC Trial version, D6, MSSQL 2005.
I have a n-tier application.
On the client side, I have a clientdataset connected via a DCOM to a provider with a TMSQuery; the CommandText of the clientdataset contains the following query:
the number of records retrieved is 67, wrong; if I delete the last field from the order by clause (d.DOCDATE), the received number of records is correctly, 1313. The query in sql studio returns 1313 records, no matter order by clause.select CLT.CLTNAME, CLT.CLTCODE, CLT.CLTID, dl.ITEMID, d.DOCDATE, d.DOCNO, t.TDOCCODE, t.TDOCNAME, dl.DVLN1QTY*dl.UMFACTOR QTY, dl.DVLN1PUNITDEV/dl.UMFACTOR Pret_unitar, dl.DVLN1QTY*dl.DVLN1PUNITDEV Valoare_vinzare, dl.DVLN1QTY*dl.DVLN1PUNITDEV-dl.ITEMPSTOC Adaos_valoric, 100*(dl.DVLN1QTY*dl.DVLN1PUNITDEV-dl.ITEMPSTOC)/dl.ITEMPSTOC Adaos_procentual
from CLT
join DOCVHD dh on CLT.CLTID=dh.CLTID
join DOCVLN1 dl on dl.DOCID=dh.DOCID
join DOCHD d on d.DOCID=dl.DOCID
join TDOC t on t.TDOCID=d.TDOCID
join ITEM on ITEM.ITEMID=dl.ITEMID
where
d.ISVALID=1 and
d.ISANULAT=0 and
t.NOEFFECT=0 and t.OPSTOC=1 and
d.DOCDATE>=20050601 and d.DOCDATE0 and dl.ITEMPSTOC is not null and
dl.ITEMID in (3884)
order by dl.ITEMID, CLT.CLTNAME, CLT.CLTCODE, d.DOCDATE
What happens?
I encountered a similar issue. In the same conditions, the query is:
select d.DOCID, ITEM.ITEMCODE, ITEM.ITEMNAME, dl.DSLN1QTY, dl.DSLN1PUNITDEV, UM.UMCODE, UM.UMNAME, ITEM.INTCODE, ITEM.TITEMID
from DOCHD d
join DOCSLN1 dl on dl.DOCID=d.DOCID
join ITEM ITEM on ITEM.ITEMID=dl.ITEMID
left outer join UM UM on UM.UMID=dl.UMID
where d.DOCID=1125754
order by d.DOCID, dl.LINEORD
which returns 1 record, wrong; if I change the alias of the first field, from d to dl (dl.DOCID), the query returns 5 records, correctly; also if I remove completely the order by clause, the result is correct.
No matter what alias I put, in the sql studio there are 5 records in the result.
In both problems, I have tested the record count returned at the server side on AfterOpen of the TMSQuery, and it is correct. I have no idea what is going wrong.
I have dug deep intro the server side and I have noticed that although the SDAC component returns the correct number of records, the provider doesn't receive all of them at the OnGetData event. Seems a communication problem between TDatasetProvider and TMSQuery component.
Finally, I build up a project with this issue and I can send to you.