THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Rob Farley

- Owner/Principal with LobsterPot Solutions (a MS Gold Partner consulting firm), Microsoft Certified Master, Microsoft MVP (SQL Server), APS/PDW trainer and leader of the SQL User Group in Adelaide, Australia. Rob is a former director of PASS, and provides consulting and training courses around the world in SQL Server and BI topics.

When someone deletes a shared data source in SSRS

Hi! - Great that you've found this page, but it's no longer here! You can find the content over at:

Published Tuesday, October 9, 2012 12:55 PM by Rob Farley



Dave Dustin said:

Very helpful and I'm glad you've published this just in case somebody needs help around 9pm at night because a Junior DBA did something "bad" during the day, this resource can be discovered.

October 15, 2012 8:35 PM

Navodita said:

Very informative.. Thanks for the details..

October 16, 2012 9:11 AM

KP said:

Great article that I found 1 day too late :-) I had this situation last night, luckily it was only 20 reports and not 400.  I've book marked this article for future reference.

October 18, 2012 3:13 PM

Valentino Vranken said:

Hi Rob,

Interesting article!  Some feedback:

"There is nothing in dbo.Catalog that describes the actual data sources that the report uses. The default data sources would be part of the Content field, as they are defined in the RDL, but when you deploy reports, you typically choose to NOT replace the data sources. Anyway, they’re not in this table. Maybe it was already considered a bit wide to throw in another ntext field, I’m not sure. They’re in dbo.DataSource instead."

I think it's logical that the report record does not show what shared data source it uses. As a report can use more than one shared data source and a shared data source can be used by more than one report, an additional table is needed to store the many-to-many relation. But I'm sure you know that? (might I have misunderstood what you meant?)

"Type tells you what kind of item it is. Some examples are 1 for a folder and 2 a report. 4 is linked reports, 5 is a data source, 6 is a report model. I forget the others for now (but feel free to put a comment giving the full list if you know it)."

3 = resource, 8 = dataset, 9 = report part (ref.



October 23, 2012 9:15 AM

Rob Farley said:

Thanks Valentino.

Regarding the potential many-to-many table required, I agree but can also point out that the parameters are populated using a single ntext column. It breaks first normal form, but there's definitely a precedent right there. I prefer the way they have it though, and actually wish parameters had their own table too.

October 23, 2012 3:36 PM

ken said:

Source control!

October 24, 2012 12:21 AM

Rob Farley said:


Yes. If you can redeploy everything and have it sort out which reports use which shared data sources, then that's even better.


October 24, 2012 12:43 AM

Frank said:

Great post! I had a slightly different issue: A new report was deployed to 70 servers but most of them didn't link the data source even though the name was the same. Slight modification to your code plus a little SQL Multi-Script and all is right and good in the world. Thanks!

October 18, 2013 10:51 AM

Curious said:

If you know of the person that deleted the datasource have them check their recycle bin and then restore it.  It's worked for me in the past without having to update the datasource in each report.

May 5, 2015 6:35 PM

Colin Baldwin said:

Lifesaver - thank you!  410 reports in my case :)

July 7, 2016 10:39 AM
Anonymous comments are disabled

This Blog



No tags have been created or used yet.


News? Haven't you read my blog?

My Company

Can't find something?

Contact Me

Twitter: @rob_farley
Skype: rob_farley

MVP (SQL Server)


Adelaide SQL UG

Privacy Statement