>Hi all
>
>I have this SQL in Report Manager. Using the old system of ODBC the SQL works w/o complaints. Enter OLE-DB the unmodified report same SQL won't work.
>
>Error Generated:

SPACKINGLISTITEMS:SQL: GROUP BY clause is missing or invalid.

>
>SQL Statement as specified in the Report Manager for SPACKINGLISTITEMS:

SELECT * FROM lv_LKsPackingListInvoice WHERE iPID = :iID

>

Bhavbhuti,
I think there is typo in SQL but it's not the matter. If I'm not wrong it selects from a view which has a 'group by' in it.
New OLEDB provider has the characteristics of VFP8 engine. In VFP8 'group by' doesn't allow you to 'group by' freely as it did in previous versions (and what it does now is the correct behavior - though I myself utilize the old behavior on purpose).

Pre VFP8 this was legal and runs w/o errors :

select fieldA, fieldB from myTable group by fieldA

What you get is every physically last entered record grouped by fieldA.
However this is not allowed in VFP8 - fieldB, second expression has no contribution to grouping and is not an aggregate function. This would exactly be same in SQL server and would error.
Luckily for those doing this on purpose VFP8 has :
set enginebehavior 70|80
and :
sys(3099,70|80) && 80 default - 8.0 compatibility
for compatibility and also this settings are supported in new OLEDB driver :)

IMHO you should revise your view to be compatible with 8.0. Till then this might show you the problem and workaround :

Local oConnection As 'ADODB.connection'
oConnection = Newobject("adodb.connection")

With oConnection
  .ConnectionString = ;
    'Provider=VFPOLEDB.1;Data source=d:\cetin\ddrive\temp\data\testdata.dbc;'
  .Open

  oRs = .Execute("select * from customer")
  ShowMe('Customer list', oRs)


* Group by at its most meaningless form :)
* Versions prior to VFP8 allowed this
* You'd get the error - simply ignore to continue

  oRs1 = .Execute("select * from customer group by cust_id")
  If Type('oRS1') = 'O'
    ShowMe('Customer list', oRs1)
  Endif

* Here we set compatibility to 70
* and can execute our meaningless group by

  .Execute("SET ENGINEBEHAVIOR 70")
  oRs2 = .Execute("select * from customer group by cust_id")
  If Type('oRS2') = 'O'
    ShowMe('Customer list', oRs2)
  Endif

  .Close
Endwith

Function ShowMe
  Lparameters tcCaption,toRecordset
  oForm = Createobject('myForm', tcCaption,toRecordset)
  oForm.Show
  Read Events
Endfunc

Define Class myform As Form
  Height = 450
  Width = 750
  Name = "Form1"

  Add Object hflex As OleControl With ;
    Top = 10, Left = 10, Height = 430, Width = 730, Name = "Hflex", ;
    OleClass = 'MSHierarchicalFlexGridLib.MSHFlexGrid'

  Procedure Init
    Lparameters tcCaption,toRecordset
    This.Caption = tcCaption
    This.hflex.Datasource = toRecordset
  Endproc
  Procedure QueryUnload
    Clear Events
  Endproc
Enddefine

Cetin