>>>Run without parameters (with hardcoded values) to make sure it works without parameters. It still looks like a bug or something gone wrong.
>>
>>I agree. Later in the program this queries works fine.
>>There is something going on the the Where OR clause that must be the issue
>>
>> lcSQLCMD=[select ljob, matno, unitcost, burden, uomcost, uom, matquant, spoiled];
>> +[ FROM bbmatdc ];
>> +[ WHERE bbmatdc.ljob=?lcljob ];
>> +[ AND bbmatdc.costdate between ?ldstartdate and ?ldenddate]
>
>Can you explain the purpose of that query? May be you can re-think it and do without OR? Alternatively, you can re-write it using UNION instead of OR.

The point of the query is just one section of a program that gathers Puurchase Order Cost, Labor Cost, Materials Cost and Stock costs for jobs that were invoiced in a date range.

This is the work around that I would not have found without your and looking at Profiler.
That gave me the info needed. A Select statement rather than and EXEC statement
The Select did not use parameters where the EXEC did.

I would be nice to know the bug but I need to get this done. The customer is limping along without it.
I would guess if it is a bug 2005 will not get the fix

Thanks again for your help brain storming on this one.

This works

lcSQLCMD=[ SELECT arjobhd.custno, ];
+[ arjobhd.customer, ];
+[ ISNULL(ssslsman.salespn,'') as salespn, ];
+[ arjobhd.transtype, ];
+[ arjobtax.taxcode, ];
+[ IsNull(arjobhd.invno,0) as invno, ];
+[ arjob.ljob, ];
+[ bbjthead.id, ];
+[ ISNULL(SUM(arjob.price),0) as invoice, ];
+[ ISNULL(SUM(arjob.frtamt),0) as freight, ];
+[ CAST(00.00 AS decimal(10,2)) as tax, ];
+[ CAST(00.00 AS decimal(10,2)) as cost_lab, ];
+[ CAST(00.00 AS decimal(10,2)) as cost_mat, ];
+[ CAST(00.00 AS decimal(10,2)) as cost_fin, ];
+[ CAST(00.00 AS decimal(10,2)) as cost_pur, ];
+[ CAST(00.00 AS decimal(10,2)) as Totcost, ];
+[ CAST(00.00 AS decimal(10,2)) as TotInv, ];
+[ CAST(00.00 AS decimal(10,2)) as profper ];
+[ FROM arjobhd ];
+[ JOIN arjob on arjobhd.invno=arjob.invno ];
+[ JOIN arjobtax on arjobhd.invno=arjobtax.invno ];
+[ JOIN bbjthead on bbjthead.ljob=arjob.ljob ];
+[ JOIN ssslsman on arjobhd.salespnno=ssslsman.salespnno ];
+[ WHERE ]

cdStartDate= "'"+DTOC(ldStartDate)+"'"
cdEndDate= "'"+DTOC(ldEndDate)+"'"

lcSQLCMD=lcSQLCMD+"( bbjthead.closedate between "+cdStartDate+" and " +cdEndDate + " AND arjobhd.invdate IS NOT NULL AND bbjthead.closedate >= arjobhd.invdate )"
lcSQLCMD=lcSQLCMD+" OR ( arjobhd.invdate between "+cdStartDate+" and " +cdEndDate + " AND bbjthead.closedate IS NOT NULL AND invdate >= bbjthead.closedate ) "
lcSQLCMD=lcSQLCMD+" Group by arjobhd.custno, arjobhd.customer, ssslsman.salespn, arjobhd.transtype, arjobtax.taxcode, arjobhd.invno, arjob.ljob, bbjthead.id ORDER by arjob.ljob, arjobhd.invno "


SQLEXEC(gnConnect,lcSQLCMD,'jobcost')