Solved

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

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SSRS 2012 URL 11 68
Best 3rd party SQL Dashboard Reporting Tool? 4 62
How to read BOM (Byte Order Mark) from csv file. 4 45
SSRS  - Dropdown with Null 3 30
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. …
A recent questions about how to add SSRS named instances, couldn't find any that talks about SQL server 2008, anyway I decided to help by creating some screen shots. The installation is straightforward, you just pop the SQL server 2008 installati…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

821 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