May 7, 2007 at 2:11 pm
Greetings fellow SQL Geeks B-)
I am in a situation where the previous reports developer was using custom data sources to produce all the reports. We now have over 200 reports using custom data sources that need to be updated to a new server we are migrating to. The first step in tackling this I am doing is to update all the reports to use a shared data source. Then when the databases get migrated I can just change the connection in one place and it will update all the reports.
With my laziness and hate for manual tedious work I must find a way to do this programatically. I've been able to make an update script that will do it one at a time but I wanted to post on the forum in case anyone else has had experience with this before...
Here's the script:
/*
update DataSource
set
Name = 'OLAPNew',
Extension = NULL,
Link = '7A3CED93-4B28-4083-9686-3D21xxxx30F4',
CredentialRetrieval = 1,
Prompt = NULL,
ConnectionString = NULL,
UserName = NULL,
Password = NULL
from
Catalog c with (nolock)
inner join DataSource ds with (nolock) on
ds.ItemID = c.ItemID
where
c.ItemID = '3B3970EC-2A56-4E54-91CD-50DxxxxAEFC3'
*/
Thoughts?
-Ben
Ben Sullins
bensullins.com
Beer is my primary key...
May 8, 2007 at 11:20 am
It worked...so anyone looking to update data sources programatically here are the steps I took:
1.) Make a copy of the a report that uses the data source you wish to update.
2.) Change the copy of the report to use the shared data source
3.) Run the query below to get the 'link' for the shared data source
4.) Identify all the reports to be updated in your query and verify the custom data sources are the same (check the 'ConnectionString' field)
5.) Change the Name and Link to the new values you've found (Step 3) and run the update below
/* Get Link for shared data source
select
c.Path,
c.Name,
c.ItemID,
ds.Name 'DataSourceName',
ds.Link,
ds.CredentialRetrieval
from
Catalog c with (nolock)
inner join DataSource ds with (nolock) on
ds.ItemID = c.ItemID
where
c.Name = ''
order by
*/
/* Now that you have the data source name find other reports using this custom data source
select
c.Path,
c.Name,
c.ItemID,
ds.Name 'DataSourceName',
ds.Link,
ds.CredentialRetrieval
from
Catalog c with (nolock)
inner join DataSource ds with (nolock) on
ds.ItemID = c.ItemID
where
ds.Name = ''
order by
2
*/
/* Update the other reports
update DataSource
set
Name = 'OLAPNew',
Extension = NULL,
Link = '7A3CED93-4B28-4083-9686-3D2xxxxx0F4',
CredentialRetrieval = 1,
Prompt = NULL,
ConnectionString = NULL,
UserName = NULL,
Password = NULL
from
Catalog c with (nolock)
inner join DataSource ds with (nolock) on
ds.ItemID = c.ItemID
where
ds.Name = ''
*/
I will probably make this more efficient and write a tutorial about it if anyone is interested...
Cheers!
-Ben
Ben Sullins
bensullins.com
Beer is my primary key...
May 11, 2007 at 10:22 am
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply