Hello Tessitura collective Mind!
Has anyone written scripts or found strait forward code based ways to find out each of their reports -- and what procedures they use? We've got a multitude of versions, and updates and new and old reports. These reports are a combination of SSRS and Infomaker formats. What would be amazing to do is have some query to filter on to be able to find if a given procedure is linked to a certain report/SSRS or Infomaker file? Any and all suggestions are appreciated. Many thanks, -Lisa
The information about what stored procedure backs a report is stored outside of the impresario database, so it is not immediately accessible by a typical SQL query. InfoMaker reports are stored in .pbl files (good luck trying to access those programmatically) and SSRS reports are stored in the XML-based RDL format, which ultimately do live in your SQL server. If you SELECT name, CONVERT(xml, CONVERT(varbinary(max), content)) AS rdl FROM ReportServer$INSTANCENAME.dbo.Catalog WHERE type = 2, you'll find the RDL XML for all of your reports... and in theory, you could query the XML file to find your <DataSet><CommandText> elements; this is all supported in T-SQL, as a matter of fact. I don't know how to use XQuery, but I found some code from a blog post in a google cached page that actually does the trick. I'm reposting it as a snippet here: https://bitbucket.org/snippets/TN_WebShare/Ee97Bn
My recommended approach is to maintain internal documentation for all custom reports that lists the dependent database objects, just like Tessitura's documentation does for all canned reports.
Thank you. I will check this out. Also if there is ever a way to find the stored procedures from infomaker reports that would be super helpful too. Do you think anyone at the Tessitura Network know this?