Solved

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

Posted on 2014-11-23
4
122 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
  • 2
  • 2
4 Comments
 
LVL 12

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 12

Accepted Solution

by:
Koen Van Wielink earned 500 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

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. …
Introduction Earlier I wrote an article about the new lookup functions (http://www.experts-exchange.com/A_3433.html) that ship with SQL Server 2008 R2.  In this article I’m going to show you another new feature of SSRS 2008 R2, this time in the vis…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

757 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now