"Invalid argument for database" error in Enterprise for date range

[ddozer]

I am getting the error "Invalid argument for database" when refreshing a report in Crystal Enterprise. I am on Crystal 11 and am using both a stored procedure and table links in the report. The SP requires a date range parameter. It always works fine when running from Crystal on my desktop, and it works fine in Enterprise if the date range is less than one week. However, if I run for more than a week I get the invalid error in Enterprise. Any ideas?

 

[briangriffin]

It sounds like you are passing in an invalid date - 2/30/2010, for instance.

What is the format of the date range parameter, and how is that logic implemented in the SP?

 

[ddozer]

Here is the code for my procedure. If I just execute it in SQL I do not get the error:

CREATE procedure [dbo].[ProductionByItem_sp_NEW]
@StartDate datetime,
@EndDate datetime
as
SELECT t1.start_dt, t1.item_no, t1.item_desc_1, t1.item_desc_2, t1.ord_no, t1.ord_status, t1.ord_qty, t2.qty
FROM ppordfil_Sql t1 with(NOLOCK)
LEFT OUTER JOIN
(SELECT ord_no, item_no, sum(quantity) as qty
FROM Production
WHERE production.loc in ('01', '02')
AND production.doc_dt >= CONVERT(DATETIME, @StartDate, 102)
AND production.doc_dt <= CONVERT(DATETIME, @EndDate, 102)
GROUP BY ord_no, item_no) t2
ON t1.ord_no = t2.ord_no and t1.item_no = t2.item_no
WHERE t1.loc in ('01', '02')
AND (t1.start_dt >= @StartDate
AND t1.start_dt <= @EndDate
AND t1.ord_status in ('C', 'R', 'U')
OR (t1.start_dt <= @StartDate
AND t1.ord_status in ('R', 'U'))
)

 

[briangriffin]

So you are saying that if you run the sp with any date range you can't get it to blow up, right? Strange.

First, I would try creating a new report based solely on the sp - don't add any tables within Crystal. Publish and run in Enterprise. If it doesn't work, and it probably won't, then the problem isn't within the report.

Next, I would try recompiling a new sp with hardcoded dates. If that works, then there may be a problem with the convert function or a bad record. Try

select distinct doc_dt from production order by 1

and scan it for a bad date record.

 

[ddozer]

This one is driving me crazy.

I created a new report that only pulled from the sp and published it to enterprise and it works ok for any date range.

So I created a copy of my crystal report and removed the additional parameters (other than the dates needed by the sp) and published it to enterprise. It still blows up.

However, even with my original report and with my copy they both run fine in Crystal from my desktop...

This one has me baffled...

Thanks for the help!

 

[NickieP]

I get this sometimes too, reports work OK in normal CR XI but suddenly will not run in CE, even though they worked for months OK before. If I access the published report that CE uses but run it from CR, it works fine. I have an unanswered thread on this subject. I resorted to rewriting the reports as I wasted so much time trying to fix. No help to you but at least it assures you it is not you going mad!