3

On my SSRS report I have a multi-value parameter which contains 250+ uniqueidentifier data type values. This works fine with a small selection of values in the parameter dropdown, but when user chooses (select all), they get this error:

An error occurred during local report processing.
String or binary data would be truncated.

Each uniqueidentifier field is 36 characters long, which means 250 of them added together result in a 9000 character string. This is what causes the truncation to occur.

What approach should I take to handle this situation?

Edit:

Couple snapshots of the stored procedure:

ALTER PROCEDURE [dbo].[spReport]
     @StartDate as datetime
    ,@EndDate as datetime
    ,@LocationId uniqueidentifier
    ,@UserIds uniqueidentifier

@UserIds is the multi-value parameter. It is used in the where clause of the query:

WHERE (U.UserId IN (@UserIds) OR @UserIds IS NULL)
Dr. Greenthumb
  • 2,280
  • 5
  • 27
  • 33
  • 2
    this is my most wild deed while being on SO. i upvoted your question just because of your nickname – Andrey Chernukha Nov 30 '12 at 10:50
  • Does the error occur when you select "all" on the report, or is the issue in your query? – David Lawson Nov 30 '12 at 14:49
  • Can you share what your query looks like? Does it by any chance call a stored procedure with an `nvarchar(xxx)` (with xxx < 9000) perhaps? In addition, you could try using SQL profiler to snatch the query being executed and try to execute it manually. – Jeroen Nov 30 '12 at 14:49
  • @David it is an issue with the report, however if I executed the query on its own using a parameter that size, it would also fail. – Dr. Greenthumb Nov 30 '12 at 14:59
  • @Jeroen I'll update the question with a sample of the sql – Dr. Greenthumb Nov 30 '12 at 14:59
  • 2
    You can't use an SSRS multi-value parameter with a stored procedure like that. You'll need to join the values in the report, pass them as a `varchar(max)`, and then split them in the stored procedure: http://stackoverflow.com/a/9862901/124386 – Richard Deeming Nov 30 '12 at 16:12
  • @Richard I did what you suggested and that solved my problem. Post it as an answer and I'll accept it. – Dr. Greenthumb Dec 06 '12 at 09:39

3 Answers3

1

SSRS does have a limit on the size of multi-value parameters. I can't remember what it is off the top of my head, but I think you are well beyond it. (SSRS converts the multi-value parameter to a comma separated string and replaces the occurances of the variable name in the query with the string.)

So as mentioned in the comments, you've got two problems:

  1. SP's can't take multi-value parameters directly from SSRS. You'll need to do some manipulation.

  2. Your overall parameter length. This may require a little bit of creativity to solve. Some options:

    • Can you supply either a separate parameter or a special value in your existing parameter for <All Users> and then check for this in the SP, returning all values in that case. If the query is directly in SSRS (instead of a SP) something like this would work:

      ...WHERE ( U.UserId in ( @UserIds) OR '<All Users>' in ( @UserIds ) )
...
    • Can you filter the number of items in your parameter, based on earlier parameters? Such as have the user select a date range and/or department, and only return UIDs that match that range?
Jamie F
  • 23,189
  • 5
  • 61
  • 77
1

You can't use an SSRS multi-value parameter with a stored procedure like that. You'll need to join the values in the report, pass them as a varchar(max), and then split them in the stored procedure:
https://stackoverflow.com/a/9862901/124386
http://www.codeulike.com/2012/03/ssrs-multi-value-parameters-with-less.html

Community
  • 1
  • 1
Richard Deeming
  • 29,830
  • 10
  • 79
  • 151
0

Another approach is to create a user defined table type and use that instead of a varchar to pass in the selected values.

Joe Torre
  • 1