Solved

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

Posted on 2014-11-23
4
124 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MDX in SSRS v SSAS 1 1,919
SSRS Fixed Header not working in Report Viewer Control in Web app 10 231
ssrs how to deploy  data driven subscription using script 3 47
SSRS 2012 URL 11 56
Written by Valentino Vranken. Introduction: The first step of creating a SQL Server Reporting Services (SSRS) report involves setting up a connection to the data source and programming a dataset to retrieve data from that data source.  The data…
A recent question popped up and the discussion heated up regarding updating a COMMENTS (TXT) field in a table using SSRS. http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Q_27475269.html?cid=1572#a37227028 (htt…
Concerto provides fully managed cloud services and the expertise to provide an easy and reliable route to the cloud. Our best-in-class solutions help you address the toughest IT challenges, find new efficiencies and deliver the best application expe…
A company’s greatest vulnerability is their email. CEO fraud, ransomware and spear phishing attacks are the no1 threat to a company’s security. Cybercrime is responsible for the largest loss of money to companies today with losses projected to r…

939 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

10 Experts available now in Live!

Get 1:1 Help Now