Not sure that is the problem

Odd thing is if I run this in Query Analyzer of SQL 2005 and entered the dates as '06/01/2010' and '06/30/2010'
for both conditions it runs fine on the same data.

But running it as a Stored Procedure or using it in a foxpro 9 form
and SQLEXEC(gnConnect,"exec get_invoices ?ldStartDate,?ldEndDate",'jobcost')
they both fail

I used this same exact code in foxpro 6 and SQL 2000 and it ran perfectly.




>Your WHERE conditions contradict LEFT JOIN to arjobtax and bbjthead. You either don't need LEFT JOIN or WHERE conditions should be moved into LEFT JOIN for corresponding table.
>
>>I tried your suggestion.
>>Problem I ma having is the SP works fine in SQL 2000 but now is not in 2005
>>
>>Here's what I am running in SQL 2005 as parameters
>>GO
>>
>>DECLARE @return_value int
>>
>>EXEC @return_value = [dbo].[get_invoices]
>> @ldStartDate = '06/01/2010',
>> @ldEndDate = '06/30/2010'
>>
>>SELECT 'Return Value' = @return_value
>>
>>GO
>>
>>Here is what is being returned?
>>
>>Msg 0, Level 11, State 0, Line 0
>>A severe error occurred on the current command. The results, if any, should be discarded.
>>Msg 0, Level 20, State 0, Line 0
>>A severe error occurred on the current command. The results, if any, should be discarded.
>>
>>
>>I had thisin the foxpro program but it did not run
>>If I take out the OR in the Where and remove one of the conditions it works
>>If I remove the other condition is works.
>>If I have them both in the foxpro program it does not
>>So I decided to make and SQL SP.
>>It works fin in SQL 2000 but in 2005 it does not.
>>
>>I'm stumped
>>
>>
>>This is the complete SP
>>
>

>Alter   PROCEDURE get_invoices 
>>/*Create  PROCEDURE get_invoices*/
>>	@ldStartDate DateTime,
>>	@ldEndDate DateTime
>>AS
>>
>>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  
>>       LEFT JOIN arjob on arjobhd.invno=arjob.invno  
>>       LEFT JOIN arjobtax on arjobhd.invno=arjobtax.invno  
>>       LEFT JOIN bbjthead on bbjthead.ljob=arjob.ljob  
>>       LEFT JOIN ssslsman on arjobhd.salespnno=ssslsman.salespnno  
>>       WHERE  
>>       (  bbjthead.closedate  between @ldstartdate and @ldenddate 
>>          AND arjobhd.invdate IS NOT NULL 
>>          AND bbjthead.closedate >= arjobhd.invdate ) 
>>       OR 
>>       (  arjobhd.invdate between @ldstartdate and @ldenddate 
>>          AND bbjthead.closedate IS NOT NULL 
>>          AND invdate >= bbjthead.closedate ) 
>>       Group by arjobhd.custno,  
>>                arjobhd.customer, 
>>                ssslsman.salespn,              
>>                arjobhd.transtype,   
>>                arjobtax.taxcode,          
>>                arjobhd.invno, 
>>                arjob.ljob, 
>>                bbjthead.id 
>>      ORDER by arjob.ljob, arjobhd.invno
>>
>>
>