Apr 242013

Accidently killed a shared datasource for 1000 reports.  Found a handy query to update all those reports with a re-created datasource.

Updated a couple of reports manually in Sharepoint. Ran the following against the ReportServer database.

select c.Name as reportname, d.link, d.Name as datasource, d.dsid, d.itemid from Catalog c
inner join DataSource d on c.ItemID = d.ItemID
where ModifiedDate > '4/23/2013'

You are looking for the D.LINK response. This is your GUID for the second query below.

This will get you all the reports you borked.

select c.Path, ds.name
from DataSource as ds
join Catalog as c on c.ItemID = ds.ItemID
where ds.flags = ds.flags & 0x7FFFFFFD /*
and ds.Link is NULL
and ds.ConnectionString is NULL
order by path

And now update the ReportServer database.

update ds set [Flags] = [Flags] | 2, [Link] = 'YOURGUID'
output deleted.Name, deleted.DSID, deleted.ItemID, deleted.Flags
from datasource as ds
join catalog as c on c.itemid = ds.itemid
where ds.flags = ds.flags & 0x7FFFFFFD
and ds.link is NULL
and ds.connectionstring IS NULL
and path like '%folder%' /* just on the safe side I updated a couple of folders separately to make sure it worked */