0

I have an extremely strange issue with an SSRS report i'm working on. We use SQL Server 2012 availability groups and as number of groups and the nodes they run on has swelled, confusion on the part of users accessing them has increased, so I've created a report that, for a given DB, returns the correct primary and lists other related DBs as well.

The issue relates to displaying the AG. certain records were returning a null AG name, and having done some investigation, I've narrowed it down to the results from the following

Select * from sys.availability_groups AG

I have created a simple table in SSRS to take these results and when running the query in SSMS and BIDS, I get 13 records. When I deploy it and run the report, I get somewhere between 10 and 12 records, depending on the node I run it against. I am frankly at a loss how to proceed from here. I feel I've reduced the complexity of the query down to it's most simple form. I've checked there is no filtering taking place. Even performing a count(*) instead yields a result of 10 rather than 13. There is also no commonality to what is being omitted; naming conventions are standard across all AGs, they're not all on the same primary, but other AGs on the same primary do show up, and as I said, running against different primaries varies the number of records returned

Any suggestions?

Improve this question
7
  • If you get data back from SSMS under your account, have you verified proper permissions of the SSRS service account or the login being used in the data source for the report?
    – user507
    Commented Sep 29, 2015 at 11:57
  • Yeah that's all fine and as I said it's all fine except this one table, other tables that are used return data fine. I had to jump through every left join to pin all this down and everything else works correctly
    – Paul Goldstraw
    Commented Sep 29, 2015 at 12:11
  • Are you sure the records aren't returned but hidden in the report? Can you check executionlog in the ssrs database to see the actual row count?
    – Tom V
    Commented Sep 29, 2015 at 14:10
  • I will investigate and feed back, however I don't see how this could be the case as even the select count(*) statement misreports the records. I imagine that would query would take place on the server i'm querying, not in SSRS. Will respond when i've checked the executionlog
    – Paul Goldstraw
    Commented Sep 29, 2015 at 14:53
  • To confirm, i've now checked the log and can see the row count is 10
    – Paul Goldstraw
    Commented Sep 29, 2015 at 15:06

1 Answer 1

Reset to default
1

So for anyone who is interested, I figured out this was a permissions issue. As this is a report, it runs under a limited permissions login. I'd granted that login read permissions to Master and View Server State, which I thought would be enough, and sure enough, the report loads, but it seems like for some reason, if you don't have View Any Definition, your results may not be complete. I suspect (though I don't really have time to prove it) that each visible AG had at least one DB with some sort of relevant permissions, bringing it into view, but anyway, View Any Definition fixed it :) thanks for all the suggestions though

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.