List of reports with embedded SQL in SQL Server Reporting Services?

Question

I have some embedded SQL in many of our reports in Report Manager. Now I need to make some code changes for all of them, is there a way to list all these reports based on embedded SQL, and not some SP?

Thanks,
D

Answer 1

You can use the Reporting Services API to retrieve a report from a web service and search for appropriate tags in XML. The MSDN documentation for the XML schema and report definition diagrams can be found here and the diagram of the parts associated with the data source . API documentation can be found here.

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

To use this, you need to query WSDL and create a stub. The tool for this is called WSDL.EXE . If I can find it, I will post it here, but you can use C # just as easily.

There is also an rs.exe tool that comes with BIDS , which accepts the VB.net program, and its vertices and tails include Reporting Services APIs. You can use this to execute the VB.Net script from the command line - essentially, it tops and tails the program, compiles it backstage and runs it against the report server.

Answer 2

You can either do what NXC suggested, or if this is a one-time job, you can simply open the RDL file for reporting in a text editor and look for regular SQL strings such as "select", "delete", etc. for searching reports with embedded SQL