How to use a script to update the data source used for connections

Question

How to use a script to update the data source used for connections

d_george

SSCrazy

Points: 2462
More actions
December 5, 2016 at 10:39 am

#401165

I would like to use a script to update the data source used for connections to support multiple folder structures with duplicate reports.
At first, it appeared that Data Source is the cross reference for reports but that doesn’t seem to be it.
It also doesn’t seem to be the value of <rd:DataSourceID> in the XML.
How do I update a specific RDLs data source connection via a script?
SELECT TOP (2)
R.ItemID AS ReportId
, R.[Path] AS ReportPath
, R.Name AS ReportName
, D.Name AS DSName
, D.[Path] AS DSPath
, CAST(CAST(R.[Content] AS [varbinary](MAX)) AS XML) reportXML
FROM
[Catalog] AS R INNER JOIN DataSource AS X ON R.ItemID = X.ItemID
INNER JOIN [Catalog] AS D ON X.Link = D.ItemID
WHERE
R.[Type] = 2
AND D.[Type] = 5
ORDER BY
ReportPath

Viewing 6 posts - 1 through 5 (of 5 total)

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

Sue_H SSC Guru Points: 90860 More actions · Answer 1

You shouldn't do it using the tables in the ReportServer database. You need to go through the web service. One option listed here:

https://ask.sqlservercentral.com/questions/98177/modify-multiple-reports-in-ssrs-for-a-specified-da.html

There are a several powershell scripts out there to do it as well if you want to search on those.

Sue

d_george SSCrazy Points: 2462 More actions · Answer 2

Thank you but I would prefer to do it directly for several reasons.

Does anyone know how to accomplish this?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

What sort of data source do your reports use - embedded or shared? If you use a shared data source then you only need to change it in one place, which won't be the report definition. I agree with Sue, though - leave the tables well alone and use one of the supported, less dangerous ways of doing it.

John

d_george SSCrazy Points: 2462 More actions · Answer 4

Thank you.

They are shared.

What do I need to update?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

I don't know. There's a ConnectionString column in the DataSource table, but it's not in plain text. Like I said, I don't recommend hacking the tables, and I don't suppose it's even supported.

John