Thursday, December 09, 2004

Command Line SQL RS

This may be useful...

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_prog_soapapi_dev_2g2q.asp

1. Create a command script to run the .rss Reporting Services script.
rs.exe -i c:\sqlrs.rss -s http://servername/reportserver/

2. Create the rss script.

Public Sub Main()

' Render arguments
Dim result As Byte() = Nothing
Dim reportPath As String = "/ReportPath/"
Dim format As String = "Excel"
Dim historyID As String = Nothing
Dim devInfo As String = "0.125inFalse"

' Prepare report parameter.
Dim parameters(0) As ParameterValue
parameters(0) = New ParameterValue()
parameters(0).Name = "Param1Name"
parameters(0).Value = "Param1Value"

Dim credentials As DataSourceCredentials() = Nothing
Dim showHideToggle As String = Nothing
Dim encoding As String
Dim mimeType As String
Dim warnings As Warning() = Nothing
Dim reportHistoryParameters As ParameterValue() = Nothing
Dim streamIDs As String() = Nothing
Dim sh As New SessionHeader()
rs.SessionHeaderValue = sh

Try
result = rs.Render(reportPath, format, historyID, devInfo, parameters, _
credentials, showHideToggle, encoding, mimeType, reportHistoryParameters, warnings, streamIDs)
sh.SessionId = rs.SessionHeaderValue.SessionId
Console.WriteLine("SessionID after call to Render: {0}", rs.SessionHeaderValue.SessionId)
Console.WriteLine("Execution date and time: {0}", rs.SessionHeaderValue.ExecutionDateTime)
Console.WriteLine("Is new execution: {0}", rs.SessionHeaderValue.IsNewExecution)
Catch e As SoapException
Console.WriteLine(e.Detail.OuterXml)
End Try
' Write the contents of the report to an MHTML file.
Try
Dim stream As FileStream = File.Create("report.xls", result.Length)
Console.WriteLine("File created.")
stream.Write(result, 0, result.Length)
Console.WriteLine("Result written to the file.")
stream.Close()
Catch e As Exception
Console.WriteLine(e.Message)
End Try


' Dim items() As CatalogItem
' items = rs.ListChildren("/", True)

' Dim item As CatalogItem
' For Each item In items
' Console.WriteLine(item.Name)
' Next item
End Sub

2 comments:

Keller said...

I am trying to execute a .rss script which will run a report with parameters. Each time I receive the error "Unhandled exception: The session identifier is missing. A session identifier is required for this operation." Can anyone tell me how I get the session id? The script is included below - it is simple at this point.


' File: RunReport.rss
Dim format as string = "PDF"
Dim fileName as String = "C:\Export2.pdf"
Dim reportPath as String = "/reports/report5"

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
Dim mimeType as string = "pdf"
Dim warnings() AS Warning = Nothing
Dim reportHistoryParameters() As ParameterValue = Nothing
Dim streamIDs() as string = Nothing
rs.Credentials = System.Net.CredentialCache.DefaultCredentials

' Report Parameters
Dim parameters(2) As ParameterValue
parameters(0) = New ParameterValue()
parameters(0).Name = "JCN_IN"
parameters(0).Value = "VA00000001058"
parameters(1) = New ParameterValue()
parameters(1).Name = "MTC_IN"
parameters(1).Value = "00"


results = rs.Render(format, deviceInfo, "PDF", 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

Keller said...

I got it to work with the following code additions

' File: RunReport.rss
Dim format as string = "PDF"
Dim fileName as String = "C:\Export2.pdf"
Dim reportPath as String = "/reports/report3"

Public Sub Main()

' Prepare Render arguments
Dim rs as New ReportExecutionService()
Dim historyID as string = Nothing
Dim deviceInfo as string = Nothing
Dim showHide as string = Nothing
Dim results() as Byte
Dim encoding as string
Dim mimeType as string = "pdf"
Dim warnings() AS Warning = Nothing
Dim reportHistoryParameters() As ParameterValue = Nothing
Dim streamIDs() as string = Nothing
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
rs.ExecutionHeaderValue = New ExecutionHeader()

' Report Parameters
Dim parameters(2) As ParameterValue
parameters(0) = New ParameterValue()
parameters(0).Name = "JCN_IN"
parameters(0).Value = "VA00000001058"
parameters(1) = New ParameterValue()
parameters(1).Name = "MTC_IN"
parameters(1).Value = "00"



rs.LoadReport(reportPath, Nothing)


results = rs.Render(format, deviceInfo, "PDF", 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