Post reply

URGENT, delete reports from report server programmatically

  • July 22, 2011 at 8:21 am

    #244553

    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

    #1357084

    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

    #1357123

    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

    #1357126

    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

    #1357134

    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

    #1357147

    Learn RS scripting.

    Sorry no link or usefull info aside from that.

  • July 22, 2011 at 9:59 am

    #1357160

    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

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 22, 2011 at 10:04 am

    #1357165

    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

    #1357167

    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

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 22, 2011 at 10:07 am

    #1357168

    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

    #1357173

    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

    #1357175

    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

    #1357176

    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

    #1357875

    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

    #1357889

    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