A Versioning system for SSRS

  • Hi, found this sweet post now. Since it's dated 2 years ago, I wonder - did a better solution pop out in the meanwhile? this patch is wonderful, and we too have both the VS and ReportBuilder working (although ReportBuilder works much more in our project).

    we're looking for an easy plugin that connects our TFS SVN to our reports... anything newer and more comprehensive come out?

  • I have seen nothing in the market that addresses this challenge, other than what was there four years ago when I originally built this system. We still use it at my work and it continues to be very useful for those occasional moments when we need to rollback to an earlier version.

    We use Git as our primary VCS, but this system runs along side it as the two really aren't compatible. I've thought of creating a PowerShell script to pull from the table created here and push it into Git, but I don't have the scripting mojo to do it without significant effort and there just hasn't been any impetus to devote the effort.

    We are more or less happy with the way things are working.

    Hope that helps,

    Dave

  • thanks for the quick reply! I haven't found anything else either. I'm gonna adopt your system 🙂

    so cool that something you built long time ago still rocks the web. thumbs up, friend.

  • Hi,
    talking about raising from the grave 😀
    First of all thank you for sharing this nice and simple solution.

    Looking at the script i was wondering why it's used "Type != 3" instead of "Type = 2", assuming of course we only want to control versions of RDL's.
    Can someone take the time to explain it to a new guy finding its way through the SSRS Ways!

    Thank you.

  • ptm3rck - Tuesday, January 2, 2018 10:13 AM

    Hi,
    talking about raising from the grave 😀
    First of all thank you for sharing this nice and simple solution.

    Looking at the script i was wondering why it's used "Type != 3" instead of "Type = 2", assuming of course we only want to control versions of RDL's.
    Can someone take the time to explain it to a new guy finding its way through the SSRS Ways!

    Thank you.

    There is no reason you couldn't use Type=2 if RDL's are the only thing you want to track. We like to track shared datasets and datasources as well, though I have to admit I've never rolled back a datasource. And yes, we are still using the system, though with the stability of SSRS 2016, I have had much less cause to rollback because of corruption which was a more common problem in earlier versions of SSRS.

  • One thing. Not sure how it affect performance but it causes deadlocks.

     

     

    <?xml version="1.0" encoding="UTF-8"?>

    -<deadlock>

    -<victim-list>

    <victimProcess id="process18ee4633468"/>

    </victim-list>

    -<process-list>

    -<process id="process18ee4633468" xactid="156589641" waitresource="KEY: 5:72057594041991168 (d17c5d3ee874)" transactionname="user_transaction" trancount="2" taskpriority="0" status="suspended" spid="172" schedulerid="11" sbid="0" priority="0" ownerId="156589641" logused="7012" loginname="TEL2K\SQLAdmin" lockTimeout="4294967295" lockMode="S" lasttranstarted="2019-12-31T15:24:12.290" lastbatchstarted="2019-12-31T15:24:12.303" lastbatchcompleted="2019-12-31T15:24:12.303" lastattention="1900-01-01T00:00:00.303" kpid="11600" isolationlevel="read committed (2)" hostpid="5632" hostname="TRINITY" ecid="0" currentdbname="ReportServer" currentdb="5" clientoption2="128056" clientoption1="671219744" clientapp="Report Server" XDES="0x19c96830430">

    -<executionStack>

    <frame stmtstart="168" stmtend="2578" sqlhandle="0x03000500bbf2a93c695cef00e1aa000000000000000000000000000000000000000000000000000000000000" procname="ReportServer.dbo.StoreVersion" line="5">INSERT INTO ReportServer.dbo.VersionStore( ItemId,Name,ModifiedDate,ModifiedBy,InsertedDate,Def)SELECT ItemId,[Name],ModifiedDate,u.UserName ModifiedBy,GETDATE() InsertedDate,CONVERT(XML, CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX), CONTENT))) DefFROM Catalog CINNER JOIN Users UON u.UserID = c.ModifiedByIDWHERE Content IS NOT NULL AND Type != 3AND ItemID IN (SELECT c.ItemIDFROM Catalog CLEFT OUTER JOIN ( SELECT v1.ItemID,MAX(v1.modifiedDate) modifiedDateFROM VersionStore V1GROUP BY ItemID) VON C.ItemID = v.ItemID</frame>

    <frame stmtstart="890" stmtend="1220" sqlhandle="0x030005007128cc29c55b08000ea9000001000000000000000000000000000000000000000000000000000000" procname="ReportServer.dbo.SetAllProperties" line="19">UPDATE Catalog SET Property = @Property, Description = @Description, Hidden = @Hidden, ModifiedByID = @ModifiedByID, ModifiedDate = @ModifiedDate WHERE Path = @pat</frame>

    </executionStack>

    <inputbuf>Proc [Database Id = 5 Object Id = 701245553]</inputbuf>

    </process>

    -<process id="process18257045c28" xactid="156589635" waitresource="KEY: 5:72057594041991168 (417270d3c042)" transactionname="user_transaction" trancount="2" taskpriority="0" status="suspended" spid="97" schedulerid="8" sbid="0" priority="0" ownerId="156589635" logused="8116" loginname="TEL2K\SQLAdmin" lockTimeout="4294967295" lockMode="S" lasttranstarted="2019-12-31T15:24:12.290" lastbatchstarted="2019-12-31T15:24:12.303" lastbatchcompleted="2019-12-31T15:24:12.303" lastattention="1900-01-01T00:00:00.303" kpid="11396" isolationlevel="read committed (2)" hostpid="5632" hostname="TRINITY" ecid="0" currentdbname="ReportServer" currentdb="5" clientoption2="128056" clientoption1="671219744" clientapp="Report Server" XDES="0x18ab4e6c430">

    -<executionStack>

    <frame stmtstart="168" stmtend="2578" sqlhandle="0x03000500bbf2a93c695cef00e1aa000000000000000000000000000000000000000000000000000000000000" procname="ReportServer.dbo.StoreVersion" line="5">INSERT INTO ReportServer.dbo.VersionStore( ItemId,Name,ModifiedDate,ModifiedBy,InsertedDate,Def)SELECT ItemId,[Name],ModifiedDate,u.UserName ModifiedBy,GETDATE() InsertedDate,CONVERT(XML, CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX), CONTENT))) DefFROM Catalog CINNER JOIN Users UON u.UserID = c.ModifiedByIDWHERE Content IS NOT NULL AND Type != 3AND ItemID IN (SELECT c.ItemIDFROM Catalog CLEFT OUTER JOIN ( SELECT v1.ItemID,MAX(v1.modifiedDate) modifiedDateFROM VersionStore V1GROUP BY ItemID) VON C.ItemID = v.ItemID</frame>

    <frame stmtstart="890" stmtend="1220" sqlhandle="0x030005007128cc29c55b08000ea9000001000000000000000000000000000000000000000000000000000000" procname="ReportServer.dbo.SetAllProperties" line="19">UPDATE Catalog SET Property = @Property, Description = @Description, Hidden = @Hidden, ModifiedByID = @ModifiedByID, ModifiedDate = @ModifiedDate WHERE Path = @pat</frame>

    </executionStack>

    <inputbuf>Proc [Database Id = 5 Object Id = 701245553]</inputbuf>

    </process>

    </process-list>

    -<resource-list>

    -<keylock id="lock18af15ce180" objectname="ReportServer.dbo.Catalog" mode="X" indexname="IX_Catalog" hobtid="72057594041991168" dbid="5" associatedObjectId="72057594041991168">

    -<owner-list>

    <owner id="process18257045c28" mode="X"/>

    </owner-list>

    -<waiter-list>

    <waiter id="process18ee4633468" mode="S" requestType="wait"/>

    </waiter-list>

    </keylock>

    -<keylock id="lock199dca42a80" objectname="ReportServer.dbo.Catalog" mode="X" indexname="IX_Catalog" hobtid="72057594041991168" dbid="5" associatedObjectId="72057594041991168">

    -<owner-list>

    <owner id="process18ee4633468" mode="X"/>

    </owner-list>

    -<waiter-list>

    <waiter id="process18257045c28" mode="S" requestType="wait"/>

    </waiter-list>

    </keylock>

    </resource-list>

    </deadlock>

Viewing 6 posts - 46 through 50 (of 50 total)

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