Stored Procedure to maintain RSExecutionLog

Question

Stored Procedure to maintain RSExecutionLog

Perry Dyball

SSC Veteran

Points: 299
More actions
May 13, 2009 at 3:04 pm

#384584

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

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

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

babreu417 Grasshopper Points: 19 More actions · Answer 1

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 More actions · Answer 2

eric.bookbinder

Grasshopper

Points: 15

September 10, 2009 at 9:39 am

#1051797

Does this SP apply to SQL Server 2008?

chris.o.smith SSCommitted Points: 1674 More actions · Answer 3

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: 1190 More actions · Answer 4

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 More actions · Answer 5

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: 1190 More actions · Answer 6

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 More actions · Answer 7

akljfhnlaflkj

SSC Guru

Points: 76202

May 19, 2016 at 7:02 am

#1880209

Thanks for the script.

stuart.unitt 31789 SSC Journeyman Points: 87 More actions · Answer 8

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: 1190 More actions · Answer 9

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

stuart.unitt 31789 SSC Journeyman Points: 87 More actions · Answer 10

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: 1190 More actions · Answer 11

I would try this instead and skip the procedure.

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

stuart.unitt 31789 SSC Journeyman Points: 87 More actions · Answer 12

Hi Joe,

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

Much appreciated,

Stuart