Broken report, Does not call procedures

Question

Hello,

I am pulling my hair on this one. A report which was working for a long time stopped to work 2-3 weeks ago.

The report has parameters whose values depends on a parent parameter and a stored proc.

When I try the report with BIDS it works, the parameters gets the list of values according to the choice made in the parent filter.

Now, I deploy this to 3 servers. In one server it works and the in the two other it does not. The list never get populated.
There is just one difference , the server where it does work has SQL Server 2008 Sp1, the 2 other have not sp1

Next try, I set a default value to the parent parameter. When loading the report, this actually populate the value list for the child parameters but then if I click the View Report button, nothing happen. It does not start the report.

I checked security, data sources, try to rebuild in a new folder. I ran a trace to see and yes, i could see call to stored procedures when it came from BIDS but  I could not see any call when it came from a web browser.

I wonder where to look at now? Any idea? It is not calling stored procedures.

---

Thanks/Regards, Philippe Cand

Answer 1

Hi,

check the list of fixes in SP1, I guess this is your problem:

The report has parameters whose values depends on a parent parameter and a stored proc.

Incorrect results are returned when you call a stored procedure that has table-valued parameters and that has the same table variable passed for two or more parameters in SQL Server 2008.

http://support.microsoft.com/kb/968369

Regards

Answer 2

Hi Sorry. This bug is still unresolved and I just discovered another report in the same situation.

In ie I note a very specific error.

Webpage error details

User Agent: Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; WOW64; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0; .NET4.0C; InfoPath.2; .NET4.0E; MS-RTC LM 8)
Timestamp: Thu, 8 Mar 2012 18:57:11 UTC


Message: Sys.WebForms.PageRequestManagerServerErrorException: An unknown error occurred while processing the request on the server. The status code returned from the server was: 500
Line: 5
Char: 62099
Code: 0
URI: http://smdelivery.onsemi.com/Reports/ScriptResource.axd?d=lMclKyuWB7mZUg8nbdQJ6HT2RqOaUrQnuuszxX-rBFMiBZN6syrHv3ope0nlAIfbq8GB27WderVMiqimsmaFctUEfPeKAqxtOLW1bNDqOOkfhfSKD1k6jaU4kuZvBf7mR3J5lI_nPiW8RRpYMaZ8BAy28X41&t=ffffffffecf3aa46


Message: Sys.WebForms.PageRequestManagerServerErrorException: An unknown error occurred while processing the request on the server. The status code returned from the server was: 500
Line: 5
Char: 62099
Code: 0
URI: http://smdelivery.onsemi.com/Reports/ScriptResource.axd?d=lMclKyuWB7mZUg8nbdQJ6HT2RqOaUrQnuuszxX-rBFMiBZN6syrHv3ope0nlAIfbq8GB27WderVMiqimsmaFctUEfPeKAqxtOLW1bNDqOOkfhfSKD1k6jaU4kuZvBf7mR3J5lI_nPiW8RRpYMaZ8BAy28X41&t=ffffffffecf3aa46


Message: Sys.WebForms.PageRequestManagerServerErrorException: An unknown error occurred while processing the request on the server. The status code returned from the server was: 500
Line: 5
Char: 62099
Code: 0
URI: http://smdelivery.onsemi.com/Reports/ScriptResource.axd?d=lMclKyuWB7mZUg8nbdQJ6HT2RqOaUrQnuuszxX-rBFMiBZN6syrHv3ope0nlAIfbq8GB27WderVMiqimsmaFctUEfPeKAqxtOLW1bNDqOOkfhfSKD1k6jaU4kuZvBf7mR3J5lI_nPiW8RRpYMaZ8BAy28X41&t=ffffffffecf3aa46

---

Thanks/Regards, Philippe Cand

Answer 3

Error 500 is a generic server error that say "something went wrong and I can't fulfill your request." 

Are you getting this error on the two servers without SP1 or also on a server that has SP1 installed?

Answer 4

Hi,

I have just installed SQL Server 2008R2 SP1 however the problem remain after upgrade.

When looking at the source solution, I see things that are not quite right. Like if fields definition were gone and things like that.

Why would this work on a machine and not on the other is a mistery.

I tried two things, rebuild a sudo report with just two of the pull-downs and it worked.

Take a copy of the original report and remove pretty much everything until it worked again. At some point when I was left with only two pull-downs then it worked again.

I need to see where in the xml the bug is. I would rather not have to rebuild these reports from scratch. I do not know why they did stop to work.

---

Thanks/Regards, Philippe Cand

Answer 5

Hi Philippe,

in which environment were your reports developed?

And where are they not working? On SQL 2008 R2 SP1 and SQL 2008, but they work on SQL 2008 SP1?

Check if below link can help, also verify yor .rdl schema:

http://msdn.microsoft.com/en-us/library/dd207009.aspx

Regards

Answer 6

Hi, I had a Microsoft premier support ticket open for that one and the great engineer there found the issue and resolution.
It is specific to the fact that we have a lot of data. read on.

Please note that the value 1000 was too small, we had to crank it up to 10000 for the report to work again.

Hi Philippe,

I am providing you a copy of our scope agreement for your issue.

Issue Definition: When executed from ie 7 8 or 9, a report Resale at Cost and Billings shows a generic http 500 error as soon as a value is chosen in the first parameter.
Scope Agreement: Goal is to address the behavior of the report parameter selection that results in http 500 error

We will now begin working together to resolve your issue. If you do not agree with the scope defined above, or would like to amend it, please let me know as soon as possible. If you have any questions or concerns, please don't hesitate to contact me.

Here’s the summary of troubleshooting with which we resolved the issue –

1. Confirmed that the report worked fine from BIDS
2. Saw the behavior from production environment (Observed in the registry that SSRS was at 10.50.1600 which is RTM build number)
3. In testing from Sandbox environment (where SP1 was applied), we ran into the issue there as well (could be the IT team applied the security update in question only recently on that sandbox server)
4. We changed the Sandbox data sources to point to the Dev SQL Server (gravel); the report continued to fail
5. Since it is a http 500 error, meaning internal error, we reviewed the Reporting Services logs – the first message on http://support.microsoft.com/kb/2661403matched with what we were seeing!

This is the security update in question - http://technet.microsoft.com/en-us/security/bulletin/MS11-100 and http://support.microsoft.com/kb/2638420 Based on reported incidents and error logged, information in this article was used to resolve the issue - http://support.microsoft.com/kb/2661403

The Solution involved adding these –

1. To \ReportServer\web.config

(After system.web section, we added this)

  <appSettings>

    <add key="aspnet:MaxHttpCollectionKeys" value="1000" />

  </appSettings>

1. To \ReportManager\web.config

(appSettings section was already present)
    <add key="aspnet:MaxHttpCollectionKeys" value="1000" />

As discussed, make the changes to Dev and Production environments and revert with status. You’re certainly welcome to get in touch with me on any Reporting Services questions should the need arise. If there’s any additional information that I can make available, let me know.

Have a great weekend.

Best regards,

Vijay

---

Thanks/Regards, Philippe Cand