Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

ddozer

MIS
May 3, 2010
12
US
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?
 
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?

 
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'))
)
 
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.


 
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!
 
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!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top