SSRS datasource rename

Question

SSRS datasource rename

PJ_SQL

SSCertifiable

Points: 6703
More actions
February 29, 2016 at 10:41 am

#305211

Hello,
I have about 100 reports that have shared datasource.
How can I change my datasource name without having to go back to each report and point it to the new name once I make the changes.
E.g: My datasource name is Dev, I want it to change it to 'SQLReports'
Thank you.

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

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

sgmunson SSC Guru Points: 110640 More actions · Answer 1

I'm not aware of any way to do that. The existing reports aren't going to suddenly figure out what you want... they'll just fail because the data source is no longer there. Sorry...

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

WHug Ten Centuries Points: 1248 More actions · Answer 2

Open SSRS and browse to the data source. Rename it. View dependent items and check a few to make sure it worked. If it didn't work, rename it back.

sgmunson SSC Guru Points: 110640 More actions · Answer 3

If that works, it will only be for reports stored within SQL Server. Any report rendered based on the .RPT file being present solely on the file system will not experience that benefit. Such reports are often included in web pages, and it's fairly common. So, as usual, the answer is likely, "it depends", and in this case, on where the existing reports function from.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

hada79 Old Hand Points: 356 More actions · Answer 4

You could create a new datasource based on the old with only the name as the difference, and then point all the reports to the new one.

First, create the data source on the Reports website. The rest is done in SSMS

-- Step 1. Get the itemID of the current datasource

select *

from catalog

where name like 'oldDataSourceName%'

-- Step 2. Get the itemId of the new datasource

select *

from catalog

where name like 'newDataSourceName%'

-- Step 3. View all items linked to current data source (just to see how many rows you expect to update

--and verify that there are actually reports using the data source)

Select c.name, ds.itemid, ds.Link

from datasource ds

join catalog c on ds.itemid = c.itemid

where link = 'Copy Item Id from step one here'

-- Step 4. Update link to new datasource

update datasource

set link = 'Copy Item Id from step two here' -- new datasource ItemId

where link = 'Copy Item Id from step one here' -- current datasource ItemId

Test reports, then see if there are any items depending on the old datasource before deleting it.