Stored Procedure to maintain RSExecutionLog

  • Comments posted to this topic are about the item Stored Procedure to maintain RSExecutionLog

  • Thanks for the stored procedure. I seem to have some duplicates in my ExecutionLogs table though. Has anyone else had a problem like this.

  • Does this SP apply to SQL Server 2008?

  • I have also seen some duplicates. For me, the reason is because of this segment of code where we are inserting data into the RSExecutionLog table:

    INNER JOIN

    RSExecutionLog.dbo.Reports R WITH(NOLOCK)

    ON C.Name COLLATE Latin1_General_CI_AS = R.Name

    AND C.Path COLLATE Latin1_General_CI_AS = R.Path

    AND C.Type = R.ReportType

    Here, if you have 2 reports with the same name, but they have different cases, the report execution will get added twice. For example, I had a report called "dept activity report.rdl". I then changed it to "Dept Activity Report.rdl". Both report names get added to the reports table, and therefore 1 report execution gets added twice into the RSExecutionLog table. To fix this, I just changed the above code to:

    INNER JOIN

    RSExecutionLog.dbo.Reports R WITH(NOLOCK)

    ON c.ItemID = r.ReportID

    It seems like this works fine.

    Chris

  • Doesn't work on 2008r2, getting Msg 8152, Level 16, State 13, Procedure Upd_RSExecutionLog, Line 108

    String or binary data would be truncated.

  • did you figure out the problem with following message? i am getting this with sql2012

    Msg 8152, Level 16, State 13, Procedure Upd_RSExecutionLog, Line 107

    String or binary data would be truncated.

    The statement has been terminated.

  • It's been some time since I looked at this but you may want to test your stored procedure and make the value of the item in question as varchar(max) as the error indicates that the size is too small.

  • Thanks for the script.

  • I received the error message and just amended a few attributes to max in design mode.

    Has anyone got this working yet? It runs OK but it's not inserting data from report server into the RS Execution Log.

  • This only READS the log data and doesn't insert anything.

  • OK, I'm new to all this so was hoping to get some reports up and running.

    What do I need to do to get it to insert the data or, has someone got amended script that does insert?

    Ta.

  • I would try this instead and skip the procedure.

    http://www.sqlservercentral.com/articles/Reporting+Services+(SSRS)/69257/

  • Hi Joe,

    Yes! I found that yesterday and will be working on it today.

    Much appreciated,

    Stuart

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply