Sql – SSRS 2005 – Capturing RAISERROR message

error handlingreporting-servicessql serversql-server-2005

I am checking whether a user has a permission to view the report through dataset stored procedure and if the user doesn't, raiserror is called.

Is there a way to display a different message from SQL Server Reports 2005 when a stored procedure (that populates report dataset) raises error (through RAISERROR) instead of returning data?

Here is a skeleton code of dataset stored procedure

create procedure ReportSprocName
    @ClientID   int,
    @Login      sysname
as
begin
    --; check user's permission through @Login
    --; * Pseudo code *
    if @Login does not have permission begin
        raiserror(@Login does not have permission, 127, 1)
        return
    end

    select  id, name, etc...
    from    someTable   
end
GO

I am interested in two possibly solutions

  1. Displaying error message within RAISERROR
  2. Displaying a hardcoded custom message in SSRS report itself

Currently this is the default message displayed by SSRS reports
alt text

Best Answer

Why would you allow the user fire to the report if they don't have permission to view it?

To display a custom message in the SSRS report itself:

  1. Add a text field
  2. Customize the text displayed on it to your satisfaction
  3. Provide an expression for the Visibility > Hidden value (Properties Window). IE:

    = iif( count(Fields!Application_Number.Value, "YOUR-DATASOURCE-NAME") > 0, true, false)

Related Topic