Vbscript - run SSRS report save report output

Anything VBScript-related, including Windows Script Host, WMI, ADSI, and more.
Forum rules
Do not post any licensing information in this forum.

Any code longer than three lines should be added as code using the 'Select Code' dropdown menu or attached as a file.
This topic is 14 years and 3 months old and has exceeded the time allowed for comments. Please begin a new topic or use the search feature to find a similar but newer topic.
Locked
User avatar
ismailc
Posts: 37
Last visit: Tue Jun 09, 2015 12:16 am

Vbscript - run SSRS report save report output

Post by ismailc »

Good day, I need help.


In vbscript i need to run a SSRS report & save output to a file.

Code: Select all

http://biweb/Reportserver?http://...&rs:Command=Render&&rs:Format=PDF

-- opens up in pdf

But i now need to automatically run the report & save the output as a file. second choice, create a word file & insert values similar to a report & save file.

Please help - kind of in mess

Regards
User avatar
marcogsp
Posts: 16
Last visit: Wed Jan 06, 2010 12:21 am

Vbscript - run SSRS report save report output

Post by marcogsp »

I found and adapted this code from Rob van der Woude's site - many thanks Rob!

http://www.robvanderwoude.com/vbstech_i ... wnload.php

Using WinHTTP to download a PDF to a specified location:

Code: Select all

Call HTTPDownload _
("http://www.primaltools.com/docs/Script%20Signing%20Made%20Simple%20Quick%20Guide.pdf", _
"someservershare")

Sub HTTPDownload( myURL, myPath )
' This Sub downloads the FILE specified in myURL to the path specified in myPath.
'
' myURL must always end with a file name
' myPath may be a directory or a file name; in either case the directory must exist
'
' Written by Rob van der Woude
' http://www.robvanderwoude.com
'
' Based on a script found on the Thai Visa forum
' http://www.thaivisa.com/forum/index.php?showtopic=21832

    ' Standard housekeeping
    Dim i, objFile, objFSO, objHTTP, strFile, strMsg
    Const ForReading = 1, ForWriting = 2, ForAppending = 8

    ' Create a File System Object
    Set objFSO = CreateObject( "Scripting.FileSystemObject" )

    ' Check if the specified target file or folder exists,
    ' and build the fully qualified path of the target file
    If objFSO.FolderExists( myPath ) Then
        strFile = objFSO.BuildPath( myPath, Mid( myURL, InStrRev( myURL, "/" ) + 1 ) )
    ElseIf objFSO.FolderExists( Left( myPath, InStrRev( myPath, "" ) - 1 ) ) Then
        strFile = myPath
    Else
        WScript.Echo "ERROR: Target folder not found."
        Exit Sub
    End If

    ' Create or open the target file
    Set objFile = objFSO.OpenTextFile( strFile, ForWriting, True )

    ' Create an HTTP object
    Set objHTTP = CreateObject( "WinHttp.WinHttpRequest.5.1" )

    ' Download the specified URL
    objHTTP.Open "GET", myURL, False
    objHTTP.Send

    ' Write the downloaded byte stream to the target file
    For i = 1 To LenB( objHTTP.ResponseBody )
        objFile.Write Chr( AscB( MidB( objHTTP.ResponseBody, i, 1 ) ) )
    Next

    ' Close the target file
    objFile.Close( )
End Sub
Using XStandard's freely available XHttp.dll (http://www.xstandard.com/en/documentation/xhttp/). I already use their freely available xZip.dll and it works super too!

Code: Select all

Call Download _
("http://www.primaltools.com/docs/Script%20Signing%20Made%20Simple%20Quick%20Guide.pdf", _
"someserverShareScript-Signing-Made-Simple-Quick-Guide.pdf")

Sub Download( myFileURL, myDestFile )
' This function uses X-standards.com's X-HTTP component to download a file
'
' Arguments:
' myFileURL  [string] the URL of the file to be downloaded
' myDestFile [string] the fully qualified path of the downloaded "target" file
'
' Written by Rob van der Woude
' http://www.robvanderwoude.com
'
' The X-HTTP component is available at:
' http://www.xstandard.com/page.asp?p=C8AACBA3-702F-4BF0-894A-B6679AA949E6
' For more information on available functionality read:
' http://www.xstandard.com/printer-friendly.asp?id=32ADACB9-6093-452A-9464-9269867AB16E
    Dim objHTTP
    Set objHTTP = CreateObject("XStandard.HTTP")
    objHTTP.Get myFileURL
    objHTTP.SaveResponseToFile myDestFile
    Set objHTTP = Nothing
End Sub
The XHTTP component works faster, but you must also specify the filename in the path. Paths in either script can be either drive path or UNC path. Perhaps one of these scripts may work for your situation.

marcogsp2009-12-02 14:39:49
User avatar
ismailc
Posts: 37
Last visit: Tue Jun 09, 2015 12:16 am

Vbscript - run SSRS report save report output

Post by ismailc »

Thank You - so excited this sounds very possible.

If only i can get it to work - Please, Please help

I get the following Error in my vbscript code window:
Bad file name or number Location: Line: 28

Code: Select all

Call HTTPDownload ("http://biweb/Reportserver?http://srv08-za143/workspace/departments/IT/ReportsTest/R-MFS019+VendorInfo.rdl&rs:Command=Render&ProcessNo=6551&VENDOR=9999&Run=1&rs:Format=PDF", "E:")
Sub HTTPDownload( myURL, myPath )
' This Sub downloads the FILE specified in myURL to the path specified in myPath.'
' myURL must always end with a file name
' myPath may be a directory or a file name; in either case the directory must exist
'
' Written by Rob van der Woude' http://www.robvanderwoude.com
'
' Based on a script found on the Thai Visa forum
' http://www.thaivisa.com/forum/index.php?showtopic=21832 
' Standard housekeeping 
	Dim i, objFile, objFSO, objHTTP, strFile, strMsg 
	Const ForReading = 1, ForWriting = 2, ForAppending = 8 
	' Create a File System Object 
	Set objFSO = CreateObject( "Scripting.FileSystemObject" ) 
	' Check if the specified target file or folder exists, 
	' and build the fully qualified path of the target file 
	If objFSO.FolderExists( myPath ) Then 
		strFile = objFSO.BuildPath( myPath, Mid( myURL, InStrRev( myURL, "/" ) + 1 ) ) 
	ElseIf objFSO.FolderExists( Left( myPath, InStrRev( myPath, "" ) - 1 ) ) Then 
		strFile = myPath 
	Else 
		WScript.Echo "ERROR: Target folder not found." 
		Exit Sub 
	End If 
	' Create or open the target file 
	Set objFile = objFSO.OpenTextFile( strFile, ForWriting, True ) 
	' Create an HTTP object 
	Set objHTTP = CreateObject( "WinHttp.WinHttpRequest.5.1" ) 
	' Download the specified URL 
	objHTTP.Open "GET", myURL, False 
	objHTTP.Send 
	' Write the downloaded byte stream to the target file 
	For i = 1 To LenB( objHTTP.ResponseBody ) 
		objFile.Write Chr( AscB( MidB( objHTTP.ResponseBody, i, 1 ) ) ) 
		Next 
	' Close the target file 
	objFile.Close( )
End Sub


The second option i got working but the file is corrupt i can't open the file, the file has been damaged

Code: Select all

Call Download ("http://biweb/Reportserver?http://srv08-za143/workspace/departments/IT/ReportsTest/R-MFS019+VendorInfo.rdl&rs:Command=Render&ProcessNo=6551&VENDOR=9999&Run=1&rs:Format=PDF", "E:.pdf")
Sub Download( myFileURL, myDestFile )
' This function uses X-standards.com's X-HTTP component to download a file
'
' Arguments:
' myFileURL [string] the URL of the file to be downloaded
' myDestFile [string] the fully qualified path of the downloaded "target" file
'' Written by Rob van der Woude
' http://www.robvanderwoude.com'
' The X-HTTP component is available at:
' http://www.xstandard.com/page.asp?p=C8AACBA3-702F-4BF0-894A-B6679AA949E6
' For more information on available functionality read:
' http://www.xstandard.com/printer-friendly.asp?id=32ADACB9-6093-452A-9464-9269867AB16E 
	Dim objHTTP 
	Set objHTTP = CreateObject("XStandard.HTTP") 
	objHTTP.Get myFileURL 
	objHTTP.SaveResponseToFile myDestFile 
	Set objHTTP = Nothing
End Sub

Please help - Thank You for getting me this far & great hope
User avatar
marcogsp
Posts: 16
Last visit: Wed Jan 06, 2010 12:21 am

Vbscript - run SSRS report save report output

Post by marcogsp »

I had my suspicions that the lack of a file name in the URL might prove troublesome, but thought it may be worth trying anyway. Sorry it did not work. A different approach would be to create an Internet Explorer object and have the script navigate to the report server's URL. Then after the report page is loaded, use the "Save As" technique to write the report to disk. I believe I have a sample of this method somewhere in my collection, but I will have to do some searching to find it.The non-authorized errors make me think that the script is using the credentials of the Local System account, which by design does not have any network access privileges. What happens when you open a command prompt in "Run As" mode and use an account with network access to run the command prompt, and thus the script? You could also try setting up a Task Scheduler job and see if that bypasses the error. When setting up a Task Scheduler job, have the job run with an account with network access.
User avatar
marcogsp
Posts: 16
Last visit: Wed Jan 06, 2010 12:21 am

Vbscript - run SSRS report save report output

Post by marcogsp »

I gave up on the Internet Explorer object approach and went with XMLHTTP instead. This should work for any binary file download, not just PDF files. I hope this works better for you.

Code: Select all

Option Explicit
Dim oXMLhttp,oStream
Dim sURL,sFilePath

Const STATUS_OK = 200
Const BINARY_DATA = 1
Const OVERWRITE = 2
Const READY_STATE_COMPLETE = 4
Const ASYNCHRONOUS_MODE = True

sURL = "http://www.primaltools.com/docs/Script%20Signing%20Made%20Simple%20Quick%20Guide.pdf"
sFilePath = "C:Script-Signing-Made-Simple-Quick-Guide.pdf"

Set oXMLhttp = CreateObject("Msxml2.XMLHTTP.6.0") 
    oXMLhttp.Open "GET", sURL, ASYNCHRONOUS_MODE
    oXMLhttp.Send
    WScript.Echo "HTTP request sent." & VbCrLf

'Make sure page load is complete before proceeding
Do Until oXMLhttp.readyState = READY_STATE_COMPLETE
    WScript.Sleep 10
Loop
WScript.Echo "Page load complete" & VbCrLf
'Save binary file
If oXMLhttp.Status = STATUS_OK Then
    Set oStream = CreateObject("ADODB.Stream")
        With oStream
        .Open
        .Flush 'Force a buffer flush
        .Type = BINARY_DATA
        .Write oXMLhttp.ResponseBody
        .Position = 0 'Go to beginning of stream
        .SaveToFile sFilePath,OVERWRITE
        .Close
        End With
    WScript.Echo "File Saved."    
Else
    WScript.Echo "Could not retrieve the file."
End If

Set oXMLhttp = Nothing
Set oStream = Nothing
marcogsp2009-12-03 18:27:58
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Vbscript - run SSRS report save report output

Post by jvierra »

YOu might find thazt this will not work with an ASP.NET backend when it is streaming a PDF file. The file is triggered by the Adobe control and not by HTTP.

You can request the file using the SOAP protocol using the code links I posted above. It should be doable in PowerShell as it is all NET Classes code.


User avatar
marcogsp
Posts: 16
Last visit: Wed Jan 06, 2010 12:21 am

Vbscript - run SSRS report save report output

Post by marcogsp »

Is there a security certificate loaded on the server? I've read of some instances where a security certificate can cause problems with SOAP in some configurations.. It may also be causing problems with the other scripts that were tried. Does the server require authentication when accessing the report? There are ways of passing user/password info in these scripts.Regarding the Local System account, it best not to tinker with its permissions and use an account with network access if the need arises. Yes, the Local System Account should be able to save files to a local disk. However, access to UNC paths can be troublesome. URL paths should be okay unless some other force comes into play.Could you please tell us which version of SQL you are using? It may help with finding and testing a solution.
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Vbscript - run SSRS report save report output

Post by jvierra »

Marco - note that the report does not come from SQS but is always generated by the reportserver service. THe web page requests a report to be rendered to a format. Authentication is always required and is usually always set to "Integrated" and is managed by the web server.

YOu cannot pull a PDF report through a URL without setting up the correct syntax. The report will be streamed and will trigger the control to load unless otherwise managed by the CLIENT code which is not always possible due to security restrictions. Client code cannot write to the disk directly.

I am not saying what you are tryng is impossible however I do think it may be.


jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Vbscript - run SSRS report save report output

Post by jvierra »

Fermat's last laugh was a good one. Can't wait for the sequel.

Yes - the impossible can be done as long as time and cost are ignored.

Using RM to schedule a report to a file sounds like a much more Microsofty type of approach although us old Unix guys still like to script stuff for fun - espexially when we are told it is impossible.

Thanks Marco.

(PS: I live near Princeton and had to go there just so I could stand and sip a brew where Andrew once stood. Did the same thing for Einstein and Feynman.) Nice town.

User avatar
jennic
Posts: 2
Last visit: Sun Dec 06, 2009 1:53 am

Vbscript - run SSRS report save report output

Post by jennic »




Hi Ismailc

All i have is the link i provide to the users.

The users now don't want the report to be saved automatically to a location instead of being view in url.


Try this one single command in biterscripting ( http://www.biterscripting.com ).


cat "http://biweb/Reportserver?http://...&rs ... Format=PDF" > "C:/output.file"



It will save the output written by the URL to file C:/output.file .


This topic is 14 years and 3 months old and has exceeded the time allowed for comments. Please begin a new topic or use the search feature to find a similar but newer topic.
Locked