I'm working on SSRS
Report.
Recently I have changed my folder location of all SSRS
report to different path. Also I need to change Datasource path. (As I have moved the reports again I need to set Datasource) So I can no it manually by going to Manage
.
But is there any shortcut way to set Datasource path to all SSRS
reports?
Finally, I found the solution and made my day.
I have wasted 5 hours of time to find the solution.
To update the shared Datasource
path - Need to update Datasource
table of Report Server
.
SELECT cLog.Path,dSource.name
FROM DataSource AS dSource
JOIN CATALOG AS cLog ON cLog.ItemID = dSource.ItemID
WHERE dSource.flags = dSource.flags AND dSource.Link IS NULL AND dSource.ConnectionString IS NULL
AND dSource.NAME = 'NameofDatasource' AND cLog.path LIKE '%foldername%'
ORDER BY
PATH
It will show all the reports which has no connection string. So now, you have to update with your [Link]
.
Note: To Get latest [Link]
- You need to set up one of reports by manually setting the Datasource
path and execute below query this will provide you latest [Link]
.
SELECT ds.Link
FROM DataSource AS ds
JOIN CATALOG AS c ON c.ItemID = ds.ItemID
WHERE ds.NAME = 'NameofDatasource' AND c.path LIKE '%foldername%' AND ds.Link IS NOT NULL
Now, only remains to update it with the same where clause. So it will update latest [Link]
to all reports.
UPDATE dSource set [Flags] = [Flags] | 2, [Link] = 'PutLatestLink'
FROM DataSource AS dSource
JOIN CATALOG AS cLog ON cLog.ItemID = dSource.ItemID
WHERE dSource.flags = dSource.flags AND dSource.Link IS NULL AND dSource.ConnectionString IS NULL
AND dSource.NAME = 'NameofDatasource' AND cLog.path LIKE '%foldername%'
Note: Please be careful if you directly execute this query on live server. For the first time, Try to update only one particular report with above query and then do for others if it really needs and worked fine.