List reports with inline SQL in SQL Server Reporting Services?

Question

I have a couple of inline SQL in many of our reports on the report manager. Now I need to do some code change for all of them, is there a way to list all those reports based on the inline SQL and not any SP?

Thanks,
D

Answer 1

You can use the reporting services API to get the report from the web service and do a search for the appropriate tags in the XML. The MSDN documentation for the report definition XML schema and diagrams can be found here and a diagram of the data source related parts here. The API documentation can be found here.

You can write .Net programs that upload and download reports from the Reporting Services API, using ReportingService.GetReportDefinition and SetReportDefinition. You can upload or download the report definition into a buffer, which can be read or written to disk. Somewhere I have an Ironpython prototype that does this, which I'll post if I find it.

To use this, you need to query the WSDL and generate a stub. The tool to do this is called WSDL.EXE. If I can find it I'll post it here, but you could just as easily use C#.

There is also a tool called rs.exe that comes with BIDS which takes a VB.net program and tops and tails it with includes for the Reporting Services API. You can use this to execute a VB.Net script from the command line - essentially it tops and tails the program, compiles it behind the scenes and runs it against a report server.

Answer 2

You can either do what NXC suggested or if this is a one time activity, you can simply open the RDL file for the report in a text editor and search for common SQL strings like "select", "delete" etc in order to find the reports which have inline SQL