May 13, 2015 at 3:39 pm
I can see the data from the following stored procedure from within SSMS, but when I link this SP to an SSRS report, I get no data. It is a simple query. This query is actually a stored procedure that is located in a production database (not Master). I have also tried using the query directly in the report itself instead of linking to a stored procedure.
My SSRS datasource is configured with a SQL login that has both the "Public" and db_datareader roles for the Master database and the production database that contains this SP.
I get no errors, but no data either. I see all the columns from the query and can add them to a table object etc, but no detail data populates when I run it.
Any ideas on what this might be or how I might be able to further troubleshoot this issue?
Here is the query:
ALTER PROCEDURE [dbo].[KL_sp_SSRS_P21UserCount]
AS
SELECT
sysdatabases.name [Database]
, loginame [Login Name]
, login_time [Login Time]
, last_batch [Last Batch]
, COUNT(loginame) [Instances]
FROM master.sys.sysprocesses
INNER JOIN master.sys.sysdatabases
ON master.sys.sysdatabases.dbid = master.sys.sysprocesses.dbid
WHERE master.sys.sysprocesses.program_name LIKE 'PXXI/SQLCA/%'
GROUP BY sysdatabases.name, loginame, login_time, last_batch
ORDER BY sysdatabases.name, loginame
May 13, 2015 at 3:57 pm
You need to grant VIEW SERVER STATE to the SQL login or a role to which it belongs.
May 14, 2015 at 9:15 am
Thank you, I knew it had to be something simple.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply