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
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
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
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
>1. Don't convert dates to char in FoxPro. Pass them as dates. Add 1 to the EndDate in FoxPro.
>
>Change your SP to use
>
>DeteFld >=@StartDate and DateFld < @EndDate -- where EndDate is one day after the passed date
>
>There are also a couple of minor problems in VFP code - I will not concentrate on that now.
>
>>The DTOC() fixed the problem
>>
>>
>>Here's the foxpro code
>>
>>
>>ldStartDate={}
>>ldEndDate={}
>>ldStartDate=thisform.startdate.value
>>ldEndDate=thisform.enddate.value
>> ldEndDate=dtoc(ldEndDate)
>>
>> SQLEXEC(gnConnect,lcSQLCMD,'jobcost')
>>
>> * To avoid a busy connection error this must be done
>> * after SQLEXEC() is on complete
>> IF SQLGetProp( gnConnect , 'ConnectBusy')
>> SQLCANCEL(gnConnect )
>> ENDIF
>>
>> SELECT jobcost
>> GOTO TOP
>> SCAN WHILE !EOF()
>> lnInvno=0
>> lnInvno=jobcost.invno
>>
>> lcSQLCMD=[SELECT ISNULL(SUM(taxamt),0) as tax FROM arjobtax WHERE arjobtax.invno=?lnInvno]
>>
>> SQLEXEC(gnConnect,lcSQLCMD,'tax')
>> * To avoid a busy connection error this must be done
>> * after SQLEXEC() is on complete
>> IF SQLGetProp( gnConnect , 'ConnectBusy')
>> SQLCANCEL(gnConnect )
>> ENDIF
>>
>> SELECT Tax
>> REPLACE jobcost.tax with tax.tax
>>
>> ENDSCAN
>> IF USED("tax")
>> SELECT tax
>> USE
>> ENDIF
>>
>> SELECT jobcost
>> GOTO TOP
>> SCAN WHILE !EOF()
>>
>> @ 2,0 SAY "Gathering costs. Please wait..."
>>
>>etc..
>>
>>>>
>>here's the SP
>>
>>
>>SET QUOTED_IDENTIFIER ON
>>GO
>>SET ANSI_NULLS ON
>>GO
>>/*Create PROCEDURE get_invoices */
>>Alter 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
>>
>>
>>GO
>>SET QUOTED_IDENTIFIER OFF
>>GO
>>SET ANSI_NULLS ON
>>GO
>
