Stored procedure paramters in SSRS double up

views:

709

answers:

Stored procedure paramters in SSRS double up

Working with SSRS and a SQL 2000 source, I have a stored procedure that takes three parameters and returns a result set. I have a dataset with the Command Type set to StoredProcedure and the procedure name in the Query String. When I attempt to execute the procedure in Visual Studio/BIDS, the Define Query Parameters dialog box presents with each of the parameters from the procedure listed twice. Supplying values for the parameters and proceeding results in a "too many arguments specified" error.

How do I get SSRS to recognize and pass the correct number of parameters to the stored procedure?

check and make sure that you don't have extra parameters declared on the report, if you do delete the 2 extra and ensure that those are the ones being passed to the stored procedure.

also double check and make sure you didn't accidentally declare 4 parameters in the stored procedure.

DForck42 2009-04-24 20:15:48

I've tried every combination imaginable. I've tried to define and not the parameters on the report. I've tried changing the number and data type of the parameters in the stored procedure. The Define Query Parameters dialog comes up with the correct names of the parameters duplicated.

John Mo 2009-04-24 20:31:14

hmm... try creating a new report file. add a dataset and point it to the stored procedure. see if this duplicates in the new report.

DForck42 2009-04-24 20:54:22

I've deleted and recreated the dataset multiple times. It's a persistent problem.

John Mo 2009-04-24 20:59:50

yes, but did you try in a completely new report file? the parameters might be persisting in the xml code behind for the report.if you're daring you could open up the xml code and find where the extra parameters are and delete them out of the code. i've had to do this a couple of times.

DForck42 2009-04-24 21:27:54

Yes. New report file too.

John Mo 2009-04-24 22:43:21

could you post the sp code?

DForck42 2009-04-25 15:53:44

The sp itself doesn't seem to have anything to do with it. I duplicated the behavior with a simple proc: CREATE PROCEDURE TestParam @VarID int AS SELECT * FROM x WHERE y = @var

John Mo 2009-04-27 15:32:05

if you can try the whole thing on someone elses machine to see if it's not a problem with your vs setup.

DForck42 2009-04-27 17:59:24

Same results on a different machine.

John Mo 2009-04-30 20:04:36

That's weird. I guess what you could do is create a stored procedure on a different server that you know what works, and then query the other server.

DForck42 2009-04-30 20:49:17

I found a workaround for this problem:

Set the Command type to "Text"
Specify the procedure name and parameters:

EXEC procname @param1, @param2, @param3

"EXEC" and specifying the parameters are both requirements for it to work. The parameters can be named anything as long as they are prefixed with the "@" symbol. All parameters that require a value must be represented. Of course any optional parameters (those with defaults specified in the proc) must be represented in the command text if the report is going to reference them internally or present them as parameters for the user, but optional parameters do not have to be specified for the proc to run and return a result set.

John Mo 2009-05-06 14:13:47

ansaurus

tags:

views:

answers:

Stored procedure paramters in SSRS double up

related questions