July 22, 2011 at 8:21 am
hi Guys,
We have a number of reports on the report server
inside folders and subfolders.
how can we delete them all programmatically.
or from databse side
sql server 2008 r2 version
I think it should be the same for 2005 too.
thanks
July 22, 2011 at 8:26 am
Why can't you use the web interface? You could delete the root folder and everything would be gone (including permissions).
July 22, 2011 at 9:23 am
thanks for your quick reply.
is there a more simple and quicker way to do it.
if that is the only way , where can I get the code?
the other thing is , is that not possible to delete xml data of each reports from reportserver database, if yes, May I know the exact tables to update.
thanks
July 22, 2011 at 9:30 am
More simple than 1 point and click and delete?
Nope.
Scripting will take you hours to find and learn, but I know it's possible.
Why the urgent nature of this call? Sounds like you're out of space or something similar.
July 22, 2011 at 9:40 am
Let me explain more,
I have a folder called "A" and it has 50folders named "A1 to A50"
and each subfolders have 10 or more reports.
the requirement is , without deleting the folders and subfolders; is that possible to delete the reports.
your way works fine if I want to delete folder "A" , not in this case.
Thanks
July 22, 2011 at 9:47 am
Learn RS scripting.
Sorry no link or usefull info aside from that.
July 22, 2011 at 9:59 am
here's a simple example in VB: if you're not familiar with programming , you might want to call upon a peer to do the deleting for you.
'--Imports System.IO
Private Sub DeleteSomeFilesRecursively(ByVal TheFolderPath As String, ByVal FileExtensionToDelete As String)
'any files in this folder?
If Directory.Exists(TheFolderPath) Then
For Each TheFilename As String In Directory.GetFiles(TheFolderPath)
If TheFilename.EndsWith(FileExtensionToDelete, StringComparison.CurrentCultureIgnoreCase) Then
File.Delete(TheFilename)
End If
Next
'now process any sub folders.
For Each TheSubDirectory As String In Directory.GetDirectories(TheFolderPath)
DeleteSomeFilesRecursively(TheSubDirectory, FileExtensionToDelete)
Next
End If
End Sub
Lowell
July 22, 2011 at 10:04 am
I don't think VB can work here.
Even tho the files are in "folders" it's all kept in the RS database so there's nothing to loop through on the HD.
July 22, 2011 at 10:06 am
Ninja's_RGR'us (7/22/2011)
I don't think VB can work here.Even tho the files are in "folders" it's all kept in the RS database so there's nothing to loop through on the HD.
ahh...my RS skills are non-existent, but i thought since he said "programmatically" i was sniffing in the right direction.
Thanks Ninja!
Lowell
July 22, 2011 at 10:07 am
You could still probably delete folder A, the rebuild A and A1-A50 manually than you could script this without running the risk of destroying your reporting services database.
July 22, 2011 at 10:12 am
Lowell (7/22/2011)
Ninja's_RGR'us (7/22/2011)
I don't think VB can work here.Even tho the files are in "folders" it's all kept in the RS database so there's nothing to loop through on the HD.
ahh...my RS skills are non-existent, but i thought since he said "programmatically" i was sniffing in the right direction.
Thanks Ninja!
There is a rs scripting language and it seems to look live VB. I'm sure your code could apply with little mods here and there, but the files are definitely not on any drives / folders anywhere so that just won't work :ermm:
July 22, 2011 at 10:13 am
Daniel Bowlin (7/22/2011)
You could still probably delete folder A, the rebuild A and A1-A50 manually than you could script this without running the risk of destroying your reporting services database.
Ya but you also need to rebuild the permissions if you got that way. And I don't know a way to script them out before to reapply them later.
July 22, 2011 at 10:15 am
This is maybe 80% shorter than manual delete.
Assuming permissions are inherited from parent folder...
Delete all the child folders.
The re-deploy your solutions (only 1 report per sub-folder) That should recreate the folders. Then you only have to go back in and delete 1 report per folder instead of 500+ reports.
July 25, 2011 at 9:34 am
This should work (I haven't tested it against our reportserver db for obvious reasons:-D), but you should take a backup of the reportserver db first as MS do not support running code directly against the database so will not be very sympathetic if you break it by doing exactly that
declare @ParentID uniqueidentifier
--set top level of structure to remove reports from
set @ParentID = (select itemid from reportserver..catalog
where name = 'A')
--find IDs of sub-folders under 'A'
select itemid
into #Folders
from reportserver..catalog
where parentid = @ParentID
and type = 1 --folders only
--select * from #Folders
--Delete reports under the sub-folders
delete
--select *
from reportserver..catalog
where parentid in
(select itemid from #folders)
and type = 2 --reports only
If you have multiple nested levels of sub-folders you will need to modify the code accordingly, currently it will only remove reports from the sub-folders directly below folder 'A'
July 25, 2011 at 9:40 am
What about the related tables???
That's why I preffer the rs scripting option. It uses supported functions, so you can't really screw it up once the logic is sound.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply