Procedure has no parameters 1

[nevets72]

Hi everybody......I tried to open a report, but got the message below (I don't understand why this message comes up.....I've defined date_val in the stored procdure.....any ideas what I should look at? Thanks!).......


Procedure Rpt_ActsAndFore has no parameters and arguments were supplied.


The stored procedure in question is:

CREATE PROCEDURE Rpt_ActsAndFore AS
BEGIN
DECLARE @date_val datetime

SELECT @date_val = MIN
(Actual_Calendar.Actual_Calendar_Date )
FROM Actual_Calendar
WHERE Actual_Calendar.Actual_Calendar_Closed = 0

SELECT * FROM Rpt_ActFor1
Where Date <= @date_val AND
DatePart(yy,Date) = DatePart(yy,@date_val)

UNION ALL
SELECT * FROM Rpt_ActFor2
Where Date > @date_val AND
DatePart(yy,Date) = DatePart(yy,@date_val)

UNION ALL
SELECT * FROM Rpt_ActFor3
Where DatePart(yy,Date) = DatePart(yy,@date_val)

END

 

[lyanch]

Your SP isn't set up right if you want to pass in the date value.

It should look something like:

CREATE PROCEDURE Rpt_ActsAndFore
@date_val datetime
AS
BEGIN


The you will be prompted for the value of @date_val

Lisa

 

[nevets72]

Lisa,

Thanks for your response....I actually set it up that way at first, but I kept getting the following message:

Syntax error converting DATETIME from character string.

Additionally, when I tried to execute the stored procdure by itself in SQL server, I received:

Procedure Rpt_ActsAndFore expects parameter @date_val, which was not supplied.

So, I changed my definition from datatime to varchar, but still got the same messages as above.....I'm not sure what to look at next.

 

[lyanch]

The first error message 'Syntax error converting DATETIME from character string.' sounds like crystal is trying to send the date in a format your database can't understand.

What is your DB type and connection type (ODBC or native).

The second error sounds like you didn't include the datetime in the call to the procedure.

This should work for most DB's

Exec Rpt_ActsAndFore '2002 05 31 00:00:00'


Lisa

 

[nevets72]

Lisa,

Thanks....I was able to run the SP by using:

Exec Rpt_ActsAndFore 'Jan 1 2002 12:00AM'

The connection type is in fact ODBC, and I'm utilizing SQL Server 6.5......do you think I need to make a change within Crystal Reports somewhere? The date/time structure seen above is used in other reports, so I know that the format itself is ok......

Steve

 

[lyanch]

You might try updating your drivers and/or try a native connection or different ODBC (MS's instead of Crystals for instance). I personally find the native connections to have fewer bugs... but that really is just my opinion.

You might also want to consider &quot;starting over&quot; with your report and seeing if it works correctly when you add the SP as the datasource for a new report.

You could really step back and create a procedure that takes a datetime parameter and outputs the same thing. If that doesn't work, the problem is most likely with the ODBC.

Lisa