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
deanmachine333Asked:
Who is Participating?
 
santhimurthydCommented:
Check this on implementation

http://blog.summitcloud.com/2010/01/multivalue-parameters-with-stored-procedures-in-ssrs-sql/

If possiebl share ur RDL for take a look into the declaration
0
 
Lee SavidgeCommented:
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.
0
 
santhimurthydCommented:
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
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
deanmachine333Author Commented:
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?
0
 
deanmachine333Author Commented:
@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
0
 
santhimurthydCommented:
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
 Parameter
0
 
deanmachine333Author Commented:
Hiya , yep i can see that and its set to Text , and using multi list values.

 Multi Value - data text Multi Value - data text
0
 
santhimurthydCommented:
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.
0
 
deanmachine333Author Commented:
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

1
 
santhimurthydCommented:
Do you adding any single quotes to the parameter fields in the paramfield
0
 
santhimurthydCommented:
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.
0
 
deanmachine333Author Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.