Today I wanted to use the SQL Reporting Services Web Service to generate some reports and save a copy of the resultant report to a database table.
Took me quite a while on Google to find some sample code for how to do this:
Private Sub SaveDBReport()
Dim rs As New ReportingService
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
Dim strConn As String
Dim results As Byte(), image As Byte()
Dim streamids As String(), streamid As String
Dim parameters(1) As ParameterValue
strConn = "Server=servername;Initial Catalog=TempTest;user id=sa;"
parameters(0) = New ParameterValue
parameters(0).Name = "ServiceProvider"
parameters(0).Value = "19"
parameters(1) = New ParameterValue
parameters(1).Name = "InvoiceNo"
parameters(1).Value = "197"
' Render the report to HTML4.0
results = rs.Render("/Invoices/Print Invoice", "HTML4.0", _
Nothing, ">DeviceInfo>TestRS/", _
parameters, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, streamids)
' For each image stream returned by the call to render,
' render the stream and save it to the application root
For Each streamid In streamids
image = rs.RenderStream("/Invoices/Print Invoice", "HTML4.0", streamid, _
Nothing, Nothing, parameters, Nothing, Nothing)
Next
' Write the rendered report to the Web form
SqlHelper.ExecuteNonQuery(strConn, CommandType.StoredProcedure, "SaveInvoice", _
New SqlParameter("@InvoiceBlob", results))
Response.BinaryWrite(results)
End Sub