Whoops deleted a shared data source

Question

Whoops deleted a shared data source

Ells

SSCrazy Eights

Points: 9901
More actions
March 26, 2015 at 6:18 am

#316537

OK,
without giving me lectures on what a stupid thing to do. I have deployed a Report Model. For some reason some of the old reports are not working. They show "The shared Data Source reference is no longer valid"
Now as they are on a client site I do not have access to the code and need to some how fix this. After doing this I will punish myself appropriately. Oh and swear to take screen shots, backups check everything etc.
Is there a way to find the name of the data source, the reference etc. This is not just for one report. It seems to affect an unknown number of reports and obviously as I am not the author and do not have the RDL file I can not see the original data source chosen
Any help very much appreciated.
E

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

reports change very rarely...can you simply restore the ReportServer database to the day before you stepped on the data source?

if not, can you restore the database as a different name, so you can interrogate the data and find the datasource you deleted, since it would still exist in a revious backup?

if none of the above, you need to find at least one report that fails, and open it in BIDS, that would give you the name of the datasource it points to,a nd you could put it back.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lowell SSC Guru Points: 323518 More actions · Answer 2

this seems to get me most of the data sources in the current catalogs as a direct query:

WITH MyCTE

AS

(

select convert(varchar(max),CONVERT(varbinary(max),Content))As StrContent,*

from reportserver.dbo.Catalog

)

SELECT SUBSTRING(StrContent,p1.i,p2.i - p1.i),* FROM MyCTE

CROSS APPLY(SELECT CHARINDEX('<DataSource ',StrContent) As i) p1

CROSS APPLY(SELECT CHARINDEX('</DataSource>',StrContent) As i) p2

some of my reports don't have it, so i get like 58 records before i trip over a

error like this:

Msg 537, Level 16, State 5, Line 2
Invalid length parameter passed to the LEFT or SUBSTRING function.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ells SSCrazy Eights Points: 9901 More actions · Answer 3

All is now kind of calm

from a very quick look at this the tables of interest appear to be Catalog and DataSource. Querying from DataSource where link IS NULL shows the broken datasource links. Now using thhe itemid from datasource and the itemid on Catalog we are able to relate this back to reports. I manually corrected one to point to the new data source. Held on to that reports ItemID in the Catalog. Then I updated one row in the DataSource table setting the Link to be the value held by the fixed report for a single report. This allowed me to check that I could fix another report by using SQL on the back end.

Once one had been updated through SQL I very carefully picked a handfull more and worked my way through.

Once this was done we had a problem with credentials which may have been through this method of fixing the link. So the Data Source in SSRS Report Manager was amended by hand and all is well (fingers crossed)

I am assuming this is from some sort of bodged deployment of both Report Model and Data Source.

Any suggestions for how to prevent this in the future as I don't fancy doing this again.

Thanks

E