SSRS data source cannot be found error

Discovered an interesting issue after an update to one of our Line of Business applications and SQL Reporting Services that hangs off it. Users went to run a report and came up with the following error:

The data source ‘e3347f4c-8369-43ec-b3f8-09f24b0a4ad7’ cannot be found. (rsDataSourceNotFound)

Intersting, so being that we had just completed an update we sent this one straight to the vendor for support who’s response was “rebuild the report”. This was not a solution I expected or happy with and after explaining that it only started happening after an update to the system they provided, it finally moved up the support tree.

Days turned into weeks and the solution was still not provided from the vendor (with who the support ticket had gone all the way up to development). So I spent some time on it myself and discovered that it was a datasource that WAS in the report but did not exist in report server database. The report kept asking for a datasource that existed but the reference for this datasource did not exist.

I tried removing the data source and saving it back using Report Builder, however still gave the same error (apparently because the data source was still in the database and could not be removed without the reference being removed which was missing a link, as such failed). I ran through some SQL queries and found the Warehouse datasource (not the warehouse_db datasource) was pointing to a database called Modal, which no longer existed.

Now because the datasource was in the SQL database for the report, but pointing to a reference that didn’t exist in the datasource database, it failed to even open the report and allow me to change the datasource using any means, including Report Builder, ultimately because according to the report it existed, however when it came time for SQL server to cough up the link it would return “ahh we can’t find that sorry mate” and the process would crash.

I ended up going into the datasource database and changing the reference for the Warehouse datasource to the same reference as the warehouse_db datasource (at least temporarily). The report then worked and allowed me to change the datasources from Report Builder.

I believe this all happened from when I copied the original report, which had this extra datasource in it (Warehouse) pointing to the Modal database. So when the vendor development team for the product removed the Modal database and removed the shared datasource (from report server but not from the report), the problem presented itself.

This did take me a few hours to find and fix, and now having this knowledge it appears to be a rather simple solution. I would have expected that support technicians who work with this daily would have been able to at least find this issue quite quickly, without saying ‘just redo the report’.

Having said that I’ve supplied my steps below for future reference for those with similar issues in the future. The below could even pertain to the datasource to find by name.

Open SQL Management Studio and run the following query to find the ItemID of the report with the issue, this example returns ItemID of report named “Summary Report” as ‘DFCBE9FF-E4C9-4955-8AFF-59835B45D2A5’

SELECT
[ItemID],
[Name]
FROM
[ReportServer].[dbo].[Catalog]

Then once you have the ItemID of the report giving the issue run the following, in this example returns the Link of ‘E3347F4C-8369-43EC-B3F8-09F24B0A4AD7’ (our faulty data source) with name of ‘Warehouse’

SELECT
[ItemID],
[Name],
[Link]
FROM
[ReportServer].[dbo].[DataSource]
WHERE
ItemID = ‘DFCBE9FF-E4C9-4955-8AFF-59835B45D2A5’

With the Link and the Name we can change the Link to a datasource that exists in this case ‘warehouse_db’ which has a Link of ‘D69334CD-6DC7-42F2-BD1E-36BB65885252’

UPDATE
[ReportServer].[dbo].[DataSource]
SET
[Link] = ‘D69334CD-6DC7-42F2-BD1E-36BB65885252’
WHERE
ItemID = ‘DFCBE9FF-E4C9-4955-8AFF-59835B45D2A5’
AND
Name = ‘Warehouse’

I could then go in and edit the report using Report Builder and remove the ‘Warehouse’ datasource from the report and save it back without error.

Joshua Bauer
Operations Manager – Vantage Networks

If you have any questions about SQL Reporting in general or errors in using SQL Reporting Server, reach out.

Don’t forget to Follow us on LinkedIn!