How to read SQL Server Report history programatically?

Question

Hi all - a SQL Reporting Services Question - for SQL Server 2008.

Given that SQL Server Reporting Services features a Scheduler which can be used to schedule the running of SQL Reports, does anyone know a way to programatically (via C#) read a report's history from the Report Server (and then perhaps retrieve the results of the report)?

So after some more digging, it looks like I need to generate a WSDL for the Report Server and then access information by using the ReportingService object - has anyone done this before (with 2008) and can provide some pointers?

Note: looks like (according to SQL 2008 books online) the WSDL address for SQL 2008 is:

http://server/reportserver/ReportService2005.asmx?wsdl

If I can get this working, I'll post an answer up with the basic steps to implementing it :) It's a little confusing as the documentation is a mixture of SQL 2000 and SQL 2005 references!

Answer 1

Well it has soap and extensibility API, perhaps they can be used?

Answer 2

OK, so I've actually figured out how to accomplish this seemigly impossible task.

Before I begin, let me just say that if you are working with SQL Server Reporting Services 2008 (SSRS 08) and have (i.e. you have no choice) to use something like Basic auth, you'll only find a world of hurt with the WCF based Service Stubs & IIS. I'm going to blog about the configuration later.

The short answer is as follows:

• Connect (e.g. new ReportingService2005() or ReportingService2005SoapClient())

Note: It's easier to use the old (pre-WCF) ASMX service, but not impossible to use the new CF version. The Authentication takes some configuring. There are also some slight syntactic changes between versions.

• Find the report history you are looking for, e.g. ReportHistorySnapshot[] history = reportServer.ListReportHistory(@"/Reports/MyHandyReport");
• Get the HistoryID from whichever snapshot you want (returned from the ListHistoryReport)

• Now, use a ReportViewer to render the historic report, much like you would any other report, e.g.:

  ReportViewer rv = new ReportViewer();
  rv.ProcessingMode = ProcessingMode.Remote;
  rv.ServerReport.ReportServerUrl = new Uri(@"http://localhost/reportserver");
  rv.ServerReport.ReportPath = @"/Reports/MyHandyReport";
  rv.ServerReport.HistoryId = historyId;
  //...snip
  byte[] bytes = rv.ServerReport.Render("Excel", null, out mimeType, out encoding, out extension, out streamids, out warnings);

Note: you can also use the second WCF Web Service (ReportExecution2005.asmx?wsdl) as well for Report Execution