August 25, 2008 at 2:14 pm
Hey all,
This is driving me crazy ... This works just fine when the parameter is in TSQL as part of the data set, but when I call a procedure for the data set, passing multiple values, I get:
Must pass parameter number 2 and subsequent parameters as '@name = value'. After the form '@name = value' has been used, all subsequent parameters must be passed in the form '@name = value'.
Basically, what I'm doing is populating a variable 'DBName' with a simple query based on a date range parameters. This gets me my available values in a drop down. I want the user to be able to select multiple values and pass them in. I profiled my session and found that it is blowing up simply because of the way reporting services is using sp_executesql and passing the value for this parameter as separate values, vs one comma delimited.
So when I pass in multiple values, this is what the RS statement looks like:
exec sp_executesql N'EXEC [admin].[dbo].[SQLVSSReporting_Summary]
@ServerName=@ServerName
,N''db1'',N''db2'',N''db3''=@DBName
,@DateStart=@DateStart
,@DateEnd=@DateEnd',N'@ServerName nvarchar(7),@DateStart nvarchar(10),@DateEnd nvarchar(10)',@ServerName=N'myserver',@DateStart=N'2008.08.18',@DateEnd=N'2008.08.25'
When I pass in a single value, this is what the RS statement looks like:
exec sp_executesql N'EXEC [admin].[dbo].[SQLVSSReporting_Summary]
@ServerName=@ServerName
,@DBName=@DBName
,@DateStart=@DateStart
,@DateEnd=@DateEnd',N'@ServerName nvarchar(7),@DBName nvarchar(7),@DateStart nvarchar(10),@DateEnd nvarchar(10)',@ServerName=N'myserver',@DBName=N'db1',@DateStart=N'2008.08.18',@DateEnd=N'2008.08.25'
Thanks
August 26, 2008 at 6:13 am
So, I added a split function to handle a passed in value with comma's ... and still no luck.
August 26, 2008 at 6:56 am
Ok, lesson learned ... I had to use stored procedure for the command type instead of text.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply