SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Stored Procedure to maintain RSExecutionLog


Stored Procedure to maintain RSExecutionLog

Author
Message
Perry Dyball
Perry Dyball
SSC Journeyman
SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)

Group: General Forum Members
Points: 93 Visits: 94
Comments posted to this topic are about the item Stored Procedure to maintain RSExecutionLog



babreu417
babreu417
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 13
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
eric.bookbinder
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 6
Does this SP apply to SQL Server 2008?
chris.o.smith
chris.o.smith
SSC-Enthusiastic
SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)

Group: General Forum Members
Points: 189 Visits: 283
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
MaricopaJoe
SSC Veteran
SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)

Group: General Forum Members
Points: 263 Visits: 428
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
lakennedy888 80324
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 14
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
MaricopaJoe
SSC Veteran
SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)

Group: General Forum Members
Points: 263 Visits: 428
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.
Iwas Bornready
Iwas Bornready
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22844 Visits: 885
Thanks for the script.
stuart.unitt 31789
stuart.unitt 31789
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 23
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
MaricopaJoe
SSC Veteran
SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)

Group: General Forum Members
Points: 263 Visits: 428
This only READS the log data and doesn't insert anything.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search