CODEWITHSUNDEEP
reporting-services
Brandon Michael Hunter
Brandon Michael Hunter

Reputation: 1221

SSRS error handling

Is there a way to customize how SSRS reports its log? I would like SSRS to report subscription errors to a database, is there a way to do this?

Thank You

Upvotes: 0

Views: 1219

Answers (2)

Ahsan Tariq
Ahsan Tariq

Reputation: 76

SSRS have a default logging mechanism on SQL server instance being used with SSRS report. you can find the log file on the following path.

C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\LogFiles

Top most file have all the reporting server logs, open that file and navigate to the end to view most recent logs.

Upvotes: 0

Matt
Matt

Reputation: 835

SSRS already logs the status of its subscriptions to the report server on the server that your instance of SSRS is running on. You could run the following query on your ReportServer and it will show you the last run status of the Subscription.

I've used this query in conjunction with an SSIS package to copy the report server database to create a report that sends out to various people telling them of the status of the subscriptions that exist on the report server

USE ReportServer

SELECT  
CatalogParent.Name ParentName, --Folder names 
Catalog.Name ReportName, --Actual rpt name
ReportCreatedByUsers.UserName ReportCreatedByUserName, --first deployed by
Catalog.CreationDate ReportCreationDate, --deployed on
ReportModifiedByUsers.UserName ReportModifiedByUserName, --last modification by
Catalog.ModifiedDate ReportModifiedDate, 
CountExecution.CountStart TotalExecutions, --total number of executions since deployment
ExecutionLog.InstanceName LastExecutedInstanceName, --server excuted on
ExecutionLog.UserName LastExecutedUserName, --user name 
ExecutionLog.Format LastExecutedFormat, --render format
ExecutionLog.TimeStart LastExecutedTimeStart, --start time
ExecutionLog.TimeEnd LastExecutedTimeEnd, --end time
-- These times need work, not always what you expect
ExecutionLog.TimeDataRetrieval LastExecutedTimeDataRetrieval,
ExecutionLog.TimeProcessing LastExecutedTimeProcessing,
ExecutionLog.TimeRendering LastExecutedTimeRendering,
-- end
ExecutionLog.Status LastExecutedStatus, --status of the report processing (not subscription)
ExecutionLog.ByteCount LastExecutedByteCount, -- bytes returned (just because i can)
ExecutionLog.[RowCount] LastExecutedRowCount,
SubscriptionOwner.UserName SubscriptionOwnerUserName, --subscription creator
SubscriptionModifiedByUsers.UserName SubscriptionModifiedByUserName, --subscription modifier
Subscriptions.ModifiedDate SubscriptionModifiedDate, --latest modification date
Subscriptions.Description SubscriptionDescription, --what the subscription does
Subscriptions.LastStatus SubscriptionLastStatus,
Subscriptions.LastRunTime SubscriptionLastRunTime --last time the subscription ran. this may be different to the last 
-- execution time especially if report is set to cache
FROM
dbo.Catalog JOIN dbo.Catalog CatalogParent --rs catalog (all things deployed to rs)
    ON Catalog.ParentID = CatalogParent.ItemID
JOIN dbo.Users ReportCreatedByUsers --all rs users
    ON Catalog.CreatedByID = ReportCreatedByUsers.UserID
JOIN dbo.Users ReportModifiedByUsers
    ON Catalog.ModifiedByID = ReportModifiedByUsers.UserID
LEFT JOIN (SELECT
            ReportID,
            MAX(TimeStart) LastTimeStart
           FROM
            dbo.ExecutionLog --self explanatory
           GROUP BY
            ReportID
           ) LatestExecution --gets the latest execution date rather than having a list longer than life
                ON Catalog.ItemID = LatestExecution.ReportID
LEFT JOIN (SELECT
            ReportID,
            COUNT(TimeStart) CountStart
           FROM
            dbo.ExecutionLog
           GROUP BY
            ReportID
           ) CountExecution -- gets the number of executions (because we can)
                ON Catalog.ItemID = CountExecution.ReportID
LEFT JOIN dbo.ExecutionLog
    ON LatestExecution.ReportID = ExecutionLog.ReportID
        AND LatestExecution.LastTimeStart = ExecutionLog.TimeStart
LEFT JOIN dbo.Subscriptions --subscription details
    ON Catalog.ItemID = Subscriptions.Report_OID
LEFT JOIN dbo.Users SubscriptionOwner --user info
    ON Subscriptions.OwnerID = SubscriptionOwner.UserID
LEFT JOIN dbo.Users SubscriptionModifiedByUsers --user info
    ON Subscriptions.OwnerID = SubscriptionModifiedByUsers.UserID
ORDER BY
CatalogParent.Name,
Catalog.Name

Logging and reporting on the stack trace as in the LogFiles on the server is a little less straightforward!

Upvotes: 1

Related Questions