Run and save a report via command line

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

Run and save a report via command line

Author

Topic

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-09-11 : 05:46:51

Update.. so you can run the following from windows task scheduler to automatically run and provide the save/open dialog. Does anyone know how to add to this command to get it to save as a filename automatically adding year month day to the file name so each report is unique? OR maybe DTS can be used?

C:\PROGRA~1\INTERN~1\iexplore.exe http://localhost/Reportserver$SQLExpress?%2fUser+Reports%2fUser+Details+Report&rs:format=PDF

....Does SQL SERVER 2005 have any command line utilities that enables a report to be run and saved in a particular format, say PDF?

Can it be done with rs utility or sqlcmd and if so know where I could get sample scripts?

dexter.knudson
Constraint Violating Yak Guru

260 Posts

Posted - 2008-09-13 : 00:47:20

See http://www.sqljunkies.ddj.com/Article/B197B9E7-EF3D-4D70-9B5E-47B74E57EF38.scuk

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-09-18 : 05:13:10

This was the final answer:

This worked for me, save this file as RunReport.rss and save as UTF-8.

'Dim format as string = "Excel"
'Dim fileName as String = "C:\Export2.xls"
Dim format as string = "PDF"
Dim fileName as String = "C:\Export2.pdf"
Dim reportPath as String = "/User Reports/User Details Report"

Public Sub Main()

' Prepare Render arguments
Dim historyID as string = Nothing
Dim deviceInfo as string = Nothing
Dim showHide as string = Nothing
Dim results() as Byte
Dim encoding as string = Nothing
'Dim mimeType as string = "ms-excel"
'Dim extension as string = "xls"
Dim mimeType as string = "application/pdf"
Dim extension as string = "pdf"
Dim warnings() AS Warning = Nothing
Dim reportHistoryParameters() As ParameterValue = Nothing
Dim streamIDs() as string = Nothing

rs.Credentials = System.Net.CredentialCache.DefaultCredentials

Dim parameters(1) As ParameterValue
parameters(0) = New ParameterValue()
parameters(0).Name = "CombinedName"
parameters(0).Value = "Shi Ray"
parameters(1) = New ParameterValue()
parameters(1).Name = "CombPriv"
parameters(1).Value = "Users - Per1"
Dim execHeader AS New ExecutionHeader()
Dim rpt AS New ExecutionInfo
rpt = rs.LoadReport(reportPath, Nothing)
rs.SetExecutionParameters(parameters, "en-us")
rs.ExecutionHeaderValue = execHeader
rs.ExecutionHeaderValue.ExecutionID = rpt.ExecutionID
results = rs.Render(format, deviceInfo, extension, mimeType, encoding, warnings, streamIDs)

' Open a file stream and write out the report
Dim stream As FileStream = File.OpenWrite(fileName)
stream.Write(results, 0, results.Length)
stream.Close()

End Sub

Then run as:

rs -i RunReport.rss -s http://localhost/reportserver$SQLExpress -e Exec2005

Here are the references:

http://www.sqljunkies.ddj.com/Article/B197B9E7-EF3D-4D70-9B5E-47B74E57EF38.scuk
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=242968&SiteID=1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources