?
Solved

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

Posted on 2014-11-23
4
Medium Priority
?
152 Views
Last Modified: 2014-12-04
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
Comment
Question by:Papa1NZ
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 13

Expert Comment

by:Koen Van Wielink
ID: 40461223
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
 

Author Comment

by:Papa1NZ
ID: 40461451
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
 
LVL 13

Accepted Solution

by:
Koen Van Wielink earned 1500 total points
ID: 40461471
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
 

Author Closing Comment

by:Papa1NZ
ID: 40482088
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code started out as a fix for a customer that had incoming data that was hunderds of numbers and words long that was to fit in one column. The problem was that the customer did not want to split words or numbers when wrapping in the column. …
How to use Variables  and Custom code in SSRS report and Assembly reference to use compile shared code in SSRS. Its big question for all who are working with SSRS. It is easy to create assembly and refer in SSRS report, still there are some steps…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question