Stored Procedure to maintain RSExecutionLog

  • Perry Dyball

    SSC Veteran

    Points: 299

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

  • babreu417

    Grasshopper

    Points: 19

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

  • eric.bookbinder

    Grasshopper

    Points: 15

    Does this SP apply to SQL Server 2008?

  • chris.o.smith

    SSCommitted

    Points: 1663

    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

  • MaricopaJoe

    Ten Centuries

    Points: 1169

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

    String or binary data would be truncated.

  • lakennedy888 80324

    Grasshopper

    Points: 21

    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.

  • MaricopaJoe

    Ten Centuries

    Points: 1169

    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.

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the script.

  • stuart.unitt 31789

    SSC Journeyman

    Points: 77

    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.

  • MaricopaJoe

    Ten Centuries

    Points: 1169

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

  • stuart.unitt 31789

    SSC Journeyman

    Points: 77

    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.

  • MaricopaJoe

    Ten Centuries

    Points: 1169

    I would try this instead and skip the procedure.

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

  • stuart.unitt 31789

    SSC Journeyman

    Points: 77

    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