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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SSRS

From novice to tech pro — start learning today.