asked on

SSRS store proc has too many arguments

Hello ,

I have a store proc that has 3 parameters @co , @sname , @db which are contract owners, suppliers names, database. I can run the store proc in Managament Studio with multiple values per parameter and i get results, nut in SSRS when i try to select multiple values in the drop down list i get the following error -

An error occured during local report processing.
An error has occured during report proccessing.
Query execution failed for dataset 'Dataset1'
Procedure or function ... has to many arguments specified.

please can someone advise why in management studio it works but not in SSRS?

thanks

Lee

Do you need to refresh the dataset in SSRS? Run a SQL trace while it is executing so you can see what is being passed to SQL.

santhimurthyd

How your passing the value in Studio as multiple parameters and also in the reportign services, the input paramter is from Listbox of multipel selection, then check the datatype as whether the input have to be string / integer

because strign values have to be enclosed like 'va1','va2' , to enable this you need to check the datatype as string

deanmachine333

ASKER

Thanks for quick reply , looks like its putting extra '' into the code?

exec sp_executesql N'exec [ContractReports].[spGetSuplierTOverCs] N'' MARKETING MGR'',N''BUSINESS DEV'',N''COMMERCIAL DIRECTOR'', @sname, @db',N'@sname nvarchar(30),@db nvarchar(10)',@sname=N' Star Limited',@db=N'MARKETINGPRO'

any ideas how to stop that ? or why its adding extra ' ' into code when seleting multiple values in drop down list?

deanmachine333

ASKER

@santhimurthyd , i have anoterh dataset that has table of avilible vaules to be used in the dropdown list , and the data type is on Text at the moment , i dont see the option for string/interger only interger ,text,boolean,date/time,float.

thanks

santhimurthyd

You are getting the Multiple selection of values from the SSRS right

Right click the Parameter and navigate to Parameter Properties

you can see an Drop down with the DataType

You see in the attached image

deanmachine333

ASKER

Hiya , yep i can see that and its set to Text , and using multi list values.

santhimurthyd

Could you check the log file at below location

C:\Program Files\Microsoft SQL Server\MSRS10.<<Machine Name/Location>>\Reporting Services\LogFiles

In the Eventviewer

I'm sure , that you have validated, Could you please recheck, whether the store procedure your calling having same no of parameter in SSRS declaration and the SQL server.

deanmachine333

ASKER

hiya yep i have they have same amount of parameters in ssms and in ssrs i ran trace and can see that ssrs is adding in extra '' as per below,

exec sp_executesql N'exec [ContractReports].[spGetSuplierTOverCs] N'' MARKETING MGR'',N''BUSINESS DEV'',N''COMMERCIAL DIRECTOR'', @sname, @db',N'@sname nvarchar(30),@db nvarchar(10)',@sname=N' Star Limited',@db=N'MARKETINGPRO'

so for example the first param should be for Contract Owner and should look like 'MARKETING MGR,BUSINESS DEV,COMMERCIAL DIRECTOR' , and then for supplier name should be 'Star Limited' and then for database as follows 'MARKETINGPRO'

thanks

santhimurthyd

Do you adding any single quotes to the parameter fields in the paramfield

santhimurthyd

It looks like the parameters declaration getting appended
@sname, @db',N'@sname nvarchar(30),@db nvarchar(10)'

Could you open you RDL by right clicking and select "view Code" and search for the store procedure declaration and callign implementation.

It seems some wrong in the declaration.

ASKER CERTIFIED SOLUTION

santhimurthyd

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

deanmachine333

ASKER

Thanks, and yep best to add a filter with expr of the function that creates the string to table. Works brilliantly thanks! dont know why the logic behind SSRS is different would be nice if it works like it does in ssms.

thanks for your help