February 8, 2011 at 7:41 am
Client asked me today if there was a way to download all reports and report models from their ReportServer. Mentioned tool on internet for £99!!! I knocked up the VBScript utilising the following SQL:
SELECT
strPath = REPLACE(REVERSE(SUBSTRING(REVERSE([Path]), CHARINDEX('/', REVERSE([Path])), LEN([Path]))), '/', '\'),
CASE
WHEN [Type] = 2 THEN
REVERSE(SUBSTRING(REVERSE([Path]), 1, CHARINDEX('/', REVERSE([Path]))-1)) + '.rdl'
WHEN [Type] = 6 THEN
REVERSE(SUBSTRING(REVERSE([Path]), 1, CHARINDEX('/', REVERSE([Path]))-1)) + '.smdl'
END AS strFileName
, strSourcecode = CAST(CAST([Content] AS VARBINARY(MAX)) AS XML)
FROM
[Catalog]
WHERE
[Type] IN (2, 6)
VBScript:
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const ForAppending = 8
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")
Set wshell = CreateObject("wscript.shell")
strRootFolder = wshell.currentdirectory & "\" & _
Cstr(Year(Now())) _
& Cstr(Month(Now())) _
& Cstr(Day(Now())) _
& Cstr(Hour(Now())) _
& Cstr(Minute(Now())) _
& Cstr(Second(Now())) _
& "\"
If Not objFSO.FolderExists(strRootFolder) Then
Set objFolder = objFSO.CreateFolder(strRootFolder)
End If
dttmStart = Now()
objConnection.Open _
"Provider=SQLOLEDB;Data Source=NORNS\ReportServer;" & _
"Trusted_Connection=Yes;Initial Catalog=ReportServer$REPORTSERVER;" & _
"User ID=;Password=;"
strSQL = _
"SELECT" & _
"strPath = REPLACE(REVERSE(SUBSTRING(REVERSE([Path]), CHARINDEX('/', REVERSE([Path])), LEN([Path]))), '/', '\'), " & _
"CASE" & _
"WHEN [Type] = 2 THEN" & _
" REVERSE(SUBSTRING(REVERSE([Path]), 1, CHARINDEX('/', REVERSE([Path]))-1)) + '.rdl'" & _
"WHEN [Type] = 6 THEN" & _
" REVERSE(SUBSTRING(REVERSE([Path]), 1, CHARINDEX('/', REVERSE([Path]))-1)) + '.smdl'" & _
"END AS strFileName" & _
", strSourcecode = CAST(CAST([Content] AS VARBINARY(MAX)) AS XML)" & _
"FROM" & _
"[Catalog]" & _
"WHERE " & _
"[Type] IN (2, 6)"
objRecordSet.Open strSQL, objConnection, adOpenStatic, adLockOptimistic
intFileCount = 0
Do Until objRecordSet.EOF
intFileCount = intFileCount + 1
strFolder = objRecordSet.Fields(0).Value
strPath = strRootFolder
For k = 1 to Len(StrFolder)
strPath = strPath & Mid(strFolder, k, 1)
If Mid(strFolder, k, 1) = "\" Then
If Not objFSO.FolderExists(strPath) Then
Set objFolder = objFSO.CreateFolder(strPath)
End If
End If
Next
strFileName = objRecordSet.Fields(1).Value
strFullName = objFSO.BuildPath(strPath, strFileName)
Set objFile = objFSO.CreateTextFile(strFullName)
objFile.Close
Set objTextFile = objFSO.OpenTextFile (strFullName, ForAppending, True)
objTextFile.Write(objRecordSet.Fields(2).Value)
objTextFile.Close
objRecordSet.MoveNext
Loop
dttmEnd = Now()
MsgBox("Downloaded " & intFileCount & " files in " & Cstr(DateDiff("s", dttmStart, dttmEnd)) & " seconds!")
Hope this helps
February 8, 2011 at 7:49 am
Or, you could just use reporting services scripter, which is free... but hey good scripting effort
February 8, 2011 at 7:52 am
Didn't find that when looking, thanks for the link.
Client's requirements included execution from command line, hence the quick bit of vbscript (yes,yes vbscript isn't ideal, but was quick to knock together!)
Scrap above, just read that it generates the script, sorry!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply