January 8, 2014 at 10:43 am
Dale, you could put the version table in another database, but the trigger would still need to live in the ReportServer database. Since that is the case, you can't have it be completely, "pristine."
The process for restoring is to copy the XML for the report version you want into a text editor, save it as an rdl file and then upload it through the report manager, either keeping or overwriting the original version.
January 23, 2014 at 11:10 pm
Conceptually, this logic could be applied to any table as more of a change or log tracking system.
Thank you!
January 24, 2014 at 4:56 am
Love the ingenuity on this solution. It may be worth it to note that the first run of the trigger will insert an initial version of all reports, shared data sources, shared datasets, report models and report parts into the VersionStore table. If the Catalog table is very large, this could take a while and may affect temporary storage, etc.
January 24, 2014 at 5:01 am
Very nice solution. I've been looking for a way to do this as well. Has anyone been able to add "commenting" into this method? Perhaps extracting one of the property fields in the report as a pseudo comment field?
January 24, 2014 at 8:16 am
Just an update since I originally wrote this article.
We have had this running on our main production SSRS instance since October 2011. It still works great and has saved our bacon a number of times.
The one change I have had to implement as the size of the VersionStore table has grown, the report was getting slower and slower.
The solution I implemented was to have a report that listed all reports and then a second drill-through report that pulled all versions of that one report. This has resulted in the reports running quickly again, with no loss in functionality.
I am happy to hear that this solution has proved valuable to many people. We still don't have a SharePoint integrated SSRS instance, though we have upgraded to 2012. Lacking that, this has been a very useful solution.
Enjoy,
Dave
January 24, 2014 at 8:48 am
Nice! We have several "Power Users" building reports using Report Builder 3.0, and this will be helpful in supporting them. Thank you! 🙂
January 24, 2014 at 9:07 am
This definitely qualifies for SQL Spackle!
January 24, 2014 at 9:15 am
I'd love to implement David Bennett's solution, but I'd be tut-tutted for adding a table and a trigger to a Microsoft database product. Our shop standard is that no changes are to be made by us to any vendor's database - period.
Whether that is a reasonable restriction or not, I shall leave it to others to consider.
January 24, 2014 at 9:51 am
I really enjoy the simplicity of this solution. Nice work.
January 24, 2014 at 11:35 am
Nice article. However, I am having difficulty opening up the RDL file, many errors:
Warning1The report definition has an invalid target namespace 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition' which cannot be upgraded.00
I assume this is because I am only running BIDS 2008?
Also, after creating the table and trigger. When I went to SSRS and created just a folder on the Home screen, clicking save took several minutes. When I thought "the trigger is doing something", I check edhte empty VersionStore table and noticed it had populated the table with every report from our SSRS server? I was puzzled because I hadn't actually created or edited an actual report yet.
Normal?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
January 24, 2014 at 1:08 pm
As noted by some of the other posters, the first time you modify anything after the trigger is installed, it will populate every report, data set and data source into the table. That is the nature of the query. This only happens once and from then on it will only populate changed items.
January 25, 2014 at 12:30 am
This is so simple and clever! Thank you for sharing this solution with the rest of us.
March 27, 2014 at 11:08 am
Very nice David.
I made some changes to the initial load:
INSERT INTO VersionStore
/* Initial Insert to load the VersionStore */
SELECT ItemId
,[Name]
,ModifiedDate
,ReportModifiedByUsers.UserName
,GETDATE() InsertedDate
,CONVERT(XML, CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX), CONTENT))) Def
FROM Catalog
JOIN
dbo.Users ReportModifiedByUsers
ON Catalog.ModifiedByID = ReportModifiedByUsers.UserID
WHERE Content IS NOT NULL
AND [Type] != 3
AND ItemID IN ( SELECT c.ItemID
FROM Catalog C
LEFT OUTER JOIN VersionStore V
ON C.ItemID = v.ItemID
WHERE Content IS NOT NULL
AND ( v.itemID IS NULL
OR v.modifiedDate != c.ModifiedDate
) )
I'm testing how to automate the export of the xml from the report.
Some suggestions?
Ramon
April 1, 2014 at 7:13 pm
Love your solution David. Well done!
We distribute "Standard" reports to a number of client sites, whom in turn modify them for their own use. This will come in handy I'm sure.
Another suggestion to restrict the Growth of the VersionStore, would be to have a scheduled job delete / archive versions older than a certain age or retain only the last 'n' versions of the report, or a combination of both, i.e. Retain at least the last 10 versions or any versions less than 6 months old.
Additionally you could implement a "Snapshot" feature to create regular (Annual/Monthly) backups.
The possibilities for this are very interesting.
Thanks
October 27, 2014 at 2:34 pm
I'd like to suggest changing to use nvarchar when converting from the varbinary content field instead of varchar. If you have folks overseas doing dev work like we do then you'll have Unicode slip in even if it doesn't appear to be so to the naked eye. :blink:
There are probably a lot of us in the "I don't have Enterprise SharePoint or a VCS" boat. Excellent post.
Viewing 15 posts - 31 through 45 (of 50 total)
You must be logged in to reply to this topic. Login to reply