0

EDIT: It seems they're using "semanticQuery" and this is why I cant see usefull information?

enter image description here


We will be decommissioning one of our functions, and we noticed that some reports are still using it.

Is there a way to grab the "query" of a report, and then filter it?

for example:

SELECT REPORT_NAME, QUERY from Magic_DMV_report_queries where QUERY like '%dbo.badFunction%'

I tried to find some dmvs and etc but with no success even trying to get some XML information.

With this query, the "command" is not so "understandable" and I thought it would be:

    DECLARE @details AS TABLE
    (
        id INT IDENTITY(1,1) NOT NULL,
        Report              NVARCHAR(100), 
        ReportPath          NVARCHAR(500),
        DataSetName         NVARCHAR(50), 
        DataSourceName      NVARCHAR(100), 
        ComandType          NVARCHAR(50), 
        CommandText         NVARCHAR(MAX)
    )

DECLARE @report xml
DECLARE @reportname NVARCHAR(100), @reportpath NVARCHAR(500)
DECLARE @query NVARCHAR(MAX)
DECLARE db_cursor CURSOR FOR 
        SELECT Name, Path ,CAST(cast(Content AS varbinary(max)) as XML)
        FROM Catalog
        WHERE Type = 2
        ORDER BY Name
OPEN db_cursor 
FETCH NEXT FROM db_cursor INTO @reportname, @reportpath, @report
WHILE @@FETCH_STATUS = 0
    BEGIN
        INSERT INTO @details
        SELECT 
            @reportname AS Report,
            @reportpath,
            t.c.value('@Name[1]', 'nvarchar(50)') AS DataSetName, 
            t.c.value('(./*:Query/*:DataSourceName)[1]', 'nvarchar(50)') AS DataSourceName, 
            t.c.value('(./*:Query/*:CommandType)[1]', 'nvarchar(100)') AS CommandType, 
            ISNULL(t.c.value('(./*:Query/*:CommandText)[1]', 'nvarchar(MAX)'), 'Shared - ' + t.c.value('(./*:SharedDataSet/*:SharedDataSetReference)[1]', 'nvarchar(MAX)'))  AS CommandText
        FROM 
            @report.nodes('/*:Report/*:DataSets/*:DataSet') AS t(c)
        FETCH NEXT FROM db_cursor INTO @reportname, @reportpath, @report
    END
CLOSE db_cursor
DEALLOCATE db_cursor


SELECT * FROM @details ORDER BY Report, DataSetName

EIDT2:

I think I did it:

with CTE as
(SELECT Name,
cast(CAST(CAST(Content AS VARBINARY(MAX)) AS XML)as nvarchar(max)) AS ReportXML
FROM Catalog
where Type = 2)select * from cte 
where ReportXML like '%My_Cool_Procedure_or_function%'
2
  • Not really a SQL solution, but could you do a search on your source control for the function reference, or at least within your SSRS Solution (assuming you only have one Solution for all of your SSRS projects)?
    – J.D.
    Commented Jan 18, 2022 at 18:53
  • FWIW that will work until things get changed - the initial write is kept in those tables, but subsequent updates aren't viewable from within T-SQL, you need to query the SSRS API instead. Look at dataperfpro.com/… for a better way involving new-webserviceproxy, but you could also use the cmdlets at github.com/microsoft/ReportingServicesTools .
    – mbourgon
    Commented Sep 8, 2023 at 19:13

1 Answer 1

0

As J.D. says, you could search your SSRS solution or source control. Alternatively, if you have multiple SSRS solutions or projects, you could use the "Find in Files" Search option in Notepad++ (or a similar tool) to search the contents of all .rdl files in a folder or set of folders for the name of the function.

If you do not have SSRS project (i.e. if your SSRS reports only live on the report server), then you can find some information on how to download them all here.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.