cancel
Showing results for 
Search instead for 
Did you mean: 

Stored Procedure - Procedure or function xxx expects parameter yyy

Former Member
0 Kudos
578

I need to be able to create reports using stored procedures in subreports.

1. I would rather not deal with .net design but instead use the crystal reports developer.

2. I can use whatever set up and installation needed on the developer machine.

3. I am stuck with the runtime engine 4.2 on the production machine until 08/2012 when I can update the third party software that I am designing for. It's a gradebook and I can't update it in the middle of a school year.

Started this issue with Crystal Reports 10 on the developer machine and the Crystal runtime engine 10.2 on the production machine. I could create reports with stored procedures but the reports would try and remove the procedures when I verified database.

It was recommended that I update to the newest version of crystal so I bought and installed 2011. The verify database option works now and on the developer machine in crystal reports 2011 I can run the report and it works. But when I place the report on the production machine (with runtime engine 10.2) I get the following error:

CrystalDecisions.CrystalReports.Engine.InternalException: Failed to open a rowset. Details: 42000:[Microsoft][ODBC SQL Server Driver][SQL Server]Procedure or function 'vsp_Test' expects parameter '@StudentID', which was not supplied. Failed to open a rowset. Failed to export the report. Error in File C:\DOCUME1\pinnuser\LOCALS1\Temp\AAAATest2 {1101F400-85ED-464B-AB1C-C269A8A3C006}.rpt: Failed to open a rowset. ---> System.Runtime.InteropServices.COMException (0x800002D3): Failed to open a rowset. Details: 42000:[Microsoft][ODBC SQL Server Driver][SQL Server]Procedure or function 'vsp_Test' expects parameter '@StudentID', which was not supplied. Failed to open a rowset. Failed to export the report. Error in File C:\DOCUME1\pinnuser\LOCALS1\Temp\AAAATest2 {1101F400-85ED-464B-AB1C-C269A8A3C006}.rpt: Failed to open a rowset. at CrystalDecisions.ReportAppServer.Controllers.ReportSourceClass.Export(ExportOptions pExportOptions, RequestContext pRequestContext) at CrystalDecisions.ReportSource.EromReportSourceBase.ExportToStream(ExportRequestContext reqContext) --- End of inner exception stack trace --- at CrystalDecisions.ReportAppServer.ConvertDotNetToErom.ThrowDotNetException(Exception e) at CrystalDecisions.ReportSource.EromReportSourceBase.ExportToStream(ExportRequestContext reqContext) at CrystalDecisions.CrystalReports.Engine.FormatEngine.ExportToStream(ExportRequestContext reqContext) at CrystalDecisions.CrystalReports.Engine.FormatEngine.Export(ExportRequestContext reqContext) at CrystalDecisions.CrystalReports.Engine.ReportDocument.ExportToDisk(ExportFormatType formatType, String fileName) at Excelsior.Pinnacle.ReportService.CrystalReportProcessor.ProcessRequest(ReportRequest request)

I uninstalled 2011 and reinstalled v10. I get the same error now. I broke v10 apparently.

I did discover that I could fix the v10 verify database error. It was caused by using the odbc driver "SQL Server Native Client 10.0 v2007.100.1600.22". The production server was using odbc driver "SQL Server v2000.86.3959.00". I changed the odbc driver on my developer machine to "SQL Server v2000.85.1132.00" and the verify database problem went away.

However, now I can't use stored procedures anymore in reports. My old reports still work but I can't create any new ones without getting the error above. If I set database to a new stored procedure on a currently working report I also get the new error.

I have also tried installing the trial version of 2008 and it didn't work. It seems to me that the problem is in the runtime engine 4.2 not my developer machine. But I am so confused now that I have no idea what is going on.

Accepted Solutions (0)

Answers (1)

Answers (1)

Former Member
0 Kudos

NEXT STEP:

I checked the following options:

1. Options: Verify Stored Procedures on First Refresh (on database tab)

2. Report Options in subquery with stored procedure: Verify Stored Procedures on First Refresh

Now the report will run without an error but the subreport comes up blank. I ran a sql trace on the report and found that the report was running EXEC vsp_Test NULL instead of passing the variable to the procedure. The NULL value should have a studentid value in it. I did verify that I have the studentID field from the table in the main report linked to the ?@StudentID parameter in the subreport that comes from the stored procedure.

I am running circles. I get these problems no matter what version of crystal I try. I haven't tried version XI yet. I'll try it next.

Seems to me the problem is in the production server (runtime engine 10.2) but I haven't done anything with that at all. The only changes have occurred on the developer machine.

Former Member
0 Kudos

I have tried all versions of Crystal Reports now. X, XI, 2008, 2011. All with no luck.

Any response at all or ideas would be greatly appreciated.

0 Kudos

Hello,

It's a little confusing what youa re actually using...

"I have also tried installing the trial version of 2008 and it didn't work. It seems to me that the problem is in the runtime engine 4.2 not my developer machine. But I am so confused now that I have no idea what is going on."

There are no versions 4.2 in CR, is this some third party tool you are using?

If so then post or call that product for support.

Don