troubleshooting Question

Error when running Report with a second dataset calling a stored procedure to pass user details

Avatar of Papa1NZ
Papa1NZ asked on
SSRS
4 Comments1 Solution205 ViewsLast Modified:
Hi I receive an error:
System.Data.SqlClient.SqlException: SET CONTEXT_INFO option requires varbinary (128) NOT NULL parameter.

It occurs when running a report which users a second dataset. The dataset users the same trigger as the main dataset which works fine. I am using the second dataset to populate the Filter parameters.

The report actually runs ok if I run report in report  builder. However if I upload to Report Manager and run it I get the error above.

Any ideas on why this is occurring?

 It appears the Paramater @UserId might be null which is why I get the error. Is there something that stops the report from running stored procedures in the Parameters? Or an execution order which I need to change somewhere?

 FYI the Parameters populate ok if I don't use the Stored Procedure. E.g. The list is populated with 100 items. But if I use the stored procedure the list is reduced to 9. However if the Stored Procedure does not run then 0 will be returned. Hence why I think the error is that null is being returned.

But I need to stored procedure to run so I can populate the filter with appropriate user details rather than the full list.

I am hoping this is just something that I am doing wrong. E.g. Do I need to add an execution order to make sure the stored procedure executed before the parameters populate in the report?

Many thanks
ASKER CERTIFIED SOLUTION
Koen Van Wielink
Business Intelligence Specialist

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros