Last updated on July 31st, 2023 at 11:21 am
I found the oddest problem while trying to troubleshoot why an SSRS report was not working. Basically, the problem centered on a prompt in SSRS; in some cases the prompt worked and in others it didn’t. I asked myself, “What gives?”
Take the following report as an example.
Depending on the collection I chose, two out of the four service account names listed above wouldn’t drill through to their corresponding reports.
When I drilled down on NT Authority\NetworkService or localSystem, instead of seeing a report with details related to services, all I would see was a prompt asking me to second the second item. I’ve never encountered that before!
On the bright side, I didn’t seem to have any issues if I drilled down on either n/a or NT AUTHORITY\LocalService.
I manually tested the queries within SQL Server Management Studio and they passed with flying colors.
What was going on with SSRS? Next, I tested the reports within my personal lab. I saw the same prompt asking me to second the second item. Once again, I tested the queries within SQL Server Management Studio, and again, the queries passed.
Eventually I discovered the problem.
Taking NT AUTHORITY\NetworkService as our example, look closely at the highlighted prompt value in the screenshot above and compare that same value within the main report.
To make it easier for you, here are the prompts below:
NT AUTHORITY\NetworkService
NT Authority\NetworkService
What is the difference? As you can see, the word, “Authority,” is in upper case in one instance, but not in the other.
This shouldn’t matter as SQL MUST use SQL_Latin1_General_CP1_CI_AS for every install (except in China) as the CI stands for Case-Insensitive.
Why, then, does SSRS care about the case? I don’t have a good answer except to tell you that my research all said the same thing: CASE MATTERS with SSRS.
By the way, localSystem appeared as LocalSystem in another collection, which explained why that prompt didn’t work.
In order to resolve these issues I forced everything to lower case and my reports are now working fine. If you have any questions about a similar problem with a prompt in SSRS, please contact me @GarthMJ.