• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 164
  • Last Modified:

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

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
0
Papa1NZ
Asked:
Papa1NZ
  • 2
  • 2
1 Solution
 
Koen Van WielinkIT ConsultantCommented:
It's not quite clear to me what you're trying to achieve. It sounds like you have 2 stored procedures. 1 returns the report data set, and a second one used to populate a parameter with userId.
How is this stored procedure called? Are you manually selecting this userId parameter before you run the report? If not, what's the default value of the procedure? How do you know what value to select if there are multiple (9) options?
0
 
Papa1NZAuthor Commented:
Hi Koen,

Only 1 SP which is passed a UserId and another specific DashboardId. The SP returns the appropriate data for that user. E.g. A list of Personnel listed in NSW. Rather than returning all the personnel for Australia, it returns just the personnel for that region.
The User and DashboardID is used for data on the DataSet 1 which has the main information. I have created a filter which allows the user to filter Personnel.  I use another dataset so that only the distinct Personnel are populated to that filter.  This works fine. But then I add in the SP which I use for the main report and I then receive the error only when I upload and run it via Report manager. If I run the report via Report Builder/Visual Studio the report runs ok with the correct amount of users.
Any ideas?
0
 
Koen Van WielinkIT ConsultantCommented:
Can you post some code shippets of the stored procedures you use for dataset 1 and dataset 2 respectively?
It might be that you have cashed result sets on your local machine, which allows the report to run locally but not once it's deployed.
0
 
Papa1NZAuthor Commented:
Hi Koen,

I located the problem. The user I was authenticating with the Database did not have owner rights to the database. Working fine now thanks.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now