Multi-value parameter truncation

Question

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)

this is my most wild deed while being on SO. i upvoted your question just because of your nickname — Andrey Chernukha
Does the error occur when you select "all" on the report, or is the issue in your query? — David Lawson
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
@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
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: stackoverflow.com/a/9862901/124386 — Richard Deeming

Jamie F Jamie F · Accepted Answer · 2012-11-30T17:28:28

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:

SP's can't take multi-value parameters directly from SSRS. You'll need to do some manipulation.
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?

Multi-value parameter truncation

3 Answers