Procedure expects parameter which was not supplied

[jamesbodine]

In relation to the following thread (towards the end) :

http://www.tek-tips.com/viewthread.cfm?qid=876460

I am having an issue with converting from Crystal 8.5(SP3) template to Crystal 10 template.

Specificly, after I open and Save the report in Crystal 10 (SP1), and I try to do a ->Database->Verify, I get the following error:

Query Engine Error: '42000:[Microsoft][ODBC SQL Server Driver][SQL Server]Procedure '<stored_proc_name>' expects parameter '<parameter_name>, which was not supplied.

The report will run without errors if I click past the error. The report verifies fine and runs fine in Crystal 8.5 as well. The problem is I have an API call that runs this report, and it verifies the report each time (which it chokes on because the verify failes with this error message).

I have summitted an issue to Crystal on this problem as of today.....but has anyone else seen this?

Jim

 

[synapsevampire]

I haven't, but it might be connectivity related.

Try switching to OLE DB or Native (if that's an option in 10).

You might also use a Database->Set Datasource Location and repoint the report to the SP, it might help you diagnose it, or even correct it.

-k

 

[FredHelm]

Jim,

This issue sounds familiar. Turn on the SQL Profiler. Using the SQLProfilerStandard template add one more event called RPC:Starting found under the Stored Procedures group. Remember to apply the login user and database filters otherwise you we log all events to your database server. What you might uncover is that your stored procedure is being called twice. The first call is with NULL as a value to all your parameters. If your stored procedure does not have default values then you will encounter the error you mentioned.

Technical support recommended to me that I run the report through the ActiveX viewer over the web and export the report to Crystal Reports format and distribute the exported version of the rpt file to my clients. This solution worked for me and now my stored procedures are only being called once and that call has the stored procedure parameters filled in when I specify them. If you export from the Crystal Reports application, you might get the same result as exporting the report through the web.

I am convinced that importing previous versions of Crystal Report files into CR 9 or CR 10 is buggy and Business Objects refuses to admit that is the case and are unwilling to provide a hotfix. Their work arounds seem to do the trick though.

Fred

 

[jamesbodine]

Yeah, I tried the SET Database trick, and tried both the ODBC and OLE DB options, I get the same error message on both.

I am off to see if Crystal via Fred's workaround might do the trick for me.

Thanks for the input, this one has been kicking me around the last couple of days

Will let you know what happens

Jim

 

[synapsevampire]

I forgot to ask if this SP is being called through conventional SQL Server connectivity, or via ADO.

If ADO it might be confused on that level.

-k

 

[jamesbodine]

It gives the same error message on both

 

[jamesbodine]

getting a little closer. It appears to have something to do with a datetime parameter, that comes from my stored procedures......

 

[synapsevampire]

Try posting the SP code here.

Some advanced functionality isn't allowed.

-k

 

[jamesbodine]

ok. sorry about the delay. I have opened a ticket with BusinessObjects. They are now able to reproduce the issue in house (but they dont know what's causing it)

Whatever it is.....its not me, my template or what I am trying to do wo WOO HOO I dont have burn up a case on it.\

Once I hear something back I will post the answer.

Jim