Wednesday, February 3, 2010

Clearing SSRS objects

While performing integration testing with my current project, I often have to clear all of the reports, folder, datasources, etc from Reporting Services. Here is a very simple SQL script to quickly get this done.

-- This database name might be 
-- different in your environment
use reportserver 

delete from datasource

-- We need to keep the root node, so leaving
-- one catalog row.
while (select count(*) from [catalog])  > 1  
begin 
delete from [catalog] 
where  parentid is not null 
and  not 
(
itemid  in 
(
select distinct parentid 
from [catalog] 
where  parentid is not null
)
)
end

No comments:

Post a Comment