In my previous post on fixing reports after exporting Visual Studio Online project collection to the on premises server, I have forgot to mention that we have fixed the reports by running SQL queries created by a local SQL ninja who wished to stay anonymous (like all ninjas do.) He created the SQL scripts before Microsoft provided the update script that I have included in my previous post and, frankly, his scripts were more comprehensive (no offense to Microsoft support, as they have been very helpful too.) Anyways, here is customer supplied script that helped us resolve the warehouse errors and fix reports after exporting Visual Studio Online project collection:
select l.Id, A.[Changed Order], l.[Changed Order] from WorkItemsLatest L
inner
join WorkItemsAre A on l.Id = A.Id and a.[Changed Order] <> l.[Changed Order]
update WorkItemsLatest set [Changed Order] = a.[Changed Order]
from WorkItemsLatest L
inner
join WorkItemsAre A on l.Id = A.Id and a.[Changed Order] <> l.[Changed Order]
select w.id, w.[Changed Order], l.[Changed Order] from workitemswere w
inner
join workitemslatest l on w.id = l.id and w.[Changed Order] > l.[Changed Order]
inner
join
(select id,
max(rev)
as maxrev from workitemswere
group
by id) x on w.id = x.id and w.rev = x.maxrev
Update workitemswere set [Changed Order] =
cast((cast((cast(l.[Changed Order] as
binary(8)))
as
Bigint)-1)
as
binary(8))
from workitemswere w
inner
join workitemslatest l on w.id = l.id and w.[Changed Order] > l.[Changed Order]
inner
join
(select id,
max(rev)
as maxrev from workitemswere
group
by id) x on w.id = x.id and w.rev = x.maxrev
select id, rev, [Changed Order] from WorkItemsWere where [Changed Order] >
@@dbts
Update WorkItemsWere set [Changed Order] =
cast((cast((cast(z.[Changed Order] as
binary(8)))
as
Bigint)-1)
as
binary(8))
from
(select w.id, w.[Changed Order], x.rev from WorkItemsWere w
inner
join
(select id, rev, [Changed Order] from WorkItemsWere where [Changed Order] >
@@dbts) x
on w.id = x.id and x.rev = w.rev-1) z
inner
join workitemswere ww on z.id = ww.id and z.rev = ww.rev
Again, while running the script may have helped us, please RUN THE SCRIPT AT YOUR OWN RISK! AND, PLEASE, DO NOT FORGET TO BACKUP THE PROJECT COLLECTION DATABASE BEFORE YOU RUN THE SCRIPT!!!