SSRS and master.sys.sysprocesses

  • 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

  • You need to grant VIEW SERVER STATE to the SQL login or a role to which it belongs.

  • 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