{"id":233,"date":"2013-04-24T02:37:10","date_gmt":"2013-04-23T20:37:10","guid":{"rendered":"http:\/\/markwheeler.com\/wordpress\/?p=233"},"modified":"2013-04-24T02:37:10","modified_gmt":"2013-04-23T20:37:10","slug":"bulk-update-of-shared-datasource-for-ssrs-reports","status":"publish","type":"post","link":"https:\/\/markwheeler.com\/?p=233","title":{"rendered":"Bulk update of shared datasource for SSRS reports"},"content":{"rendered":"<p>Accidently killed a shared datasource for 1000 reports. \u00a0Found a <a href=\"http:\/\/sqlblog.com\/blogs\/rob_farley\/archive\/2012\/10\/09\/when-someone-deletes-a-shared-data-source-in-ssrs.aspx\">handy query<\/a> to update all those reports with a re-created datasource.<\/p>\n<p>Updated a couple of reports manually in Sharepoint. Ran the following against the ReportServer database.<\/p>\n<pre>select c.Name as reportname, d.link, d.Name as datasource, d.dsid, d.itemid from Catalog c<\/pre>\n<pre>inner join DataSource d on c.ItemID = d.ItemID<\/pre>\n<pre>where ModifiedDate &gt; '4\/23\/2013'<\/pre>\n<p>You are looking for the D.LINK response. This is your GUID for the second query below.<\/p>\n<p>This will get you all the reports you borked.<\/p>\n<pre>select c.Path, ds.name<\/pre>\n<pre>from DataSource as ds<\/pre>\n<pre>join Catalog as c on c.ItemID = ds.ItemID<\/pre>\n<pre>where ds.flags = ds.flags &amp; 0x7FFFFFFD \/*<\/pre>\n<pre>and ds.Link is NULL<\/pre>\n<pre>and ds.ConnectionString is NULL<\/pre>\n<pre>order by path<\/pre>\n<p>And now update the ReportServer database.<\/p>\n<pre>update ds set [Flags] = [Flags] | 2, [Link] = 'YOURGUID'<\/pre>\n<pre>output deleted.Name, deleted.DSID, deleted.ItemID, deleted.Flags<\/pre>\n<pre>from datasource as ds<\/pre>\n<pre>join catalog as c on c.itemid = ds.itemid<\/pre>\n<pre>where ds.flags = ds.flags &amp; 0x7FFFFFFD<\/pre>\n<pre>and ds.link is NULL<\/pre>\n<pre>and ds.connectionstring IS NULL<\/pre>\n<pre>and path like '%folder%' \/* just on the safe side I updated a couple of folders separately to make sure it worked *\/<\/pre>\n<pre><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Accidently killed a shared datasource for 1000 reports. \u00a0Found 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 [&hellip;]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"footnotes":""},"categories":[2,10,11],"tags":[111],"class_list":["post-233","post","type-post","status-publish","format-standard","hentry","category-computer","category-sharepoint","category-software","tag-ssrs"],"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/markwheeler.com\/index.php?rest_route=\/wp\/v2\/posts\/233","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/markwheeler.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/markwheeler.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/markwheeler.com\/index.php?rest_route=\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/markwheeler.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=233"}],"version-history":[{"count":0,"href":"https:\/\/markwheeler.com\/index.php?rest_route=\/wp\/v2\/posts\/233\/revisions"}],"wp:attachment":[{"href":"https:\/\/markwheeler.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=233"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/markwheeler.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=233"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/markwheeler.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=233"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}