I have a pretty extensive SQL Server query setup that performs the following functions. For each RDL report in our system (RDL is stored in a table as binary), I extract the CommandText from each dataset and store it in a table as VARCHAR(MAX). I have a separate table that holds client-created custom database objects (i.e. views, tables, stored procedures, and functions). The goal is for us to scan each report's CommandText for any references to the custom objects we've previously identified. I created a custom function that does the actual CONTAINS scan, and the CommandText is setup for FullText searching.
To be frank, everything works as I want. The issue is that the first time I run the script as a whole (Which includes building the report table to hold the report details, populating said table, creating the fulltext catalog and index, then updating the report table by querying it against the custom search function), the function does not return any results on objects that it should. If I then manually re-run the UPDATE statement that calls the function, it then returns the results as expected.
I do have all the major parts of this split up into batches (i.e. using GO) but it doesn't seem to make a difference. We really want this to be a single script if possible, so I appreciate any pointers or ideas to make sense of this. Scripts below for reference...
This is the part of my query that builds the full-text search index after my custom table that has all the report RDL details in it is populated:
CREATE UNIQUE CLUSTERED INDEX idx_ID ON dbo.tmpPremRptCmdTxt (ID)
CREATE FULLTEXT CATALOG Prem_tmpPremRptCmdTxt_FTCat
CREATE FULLTEXT INDEX ON tmpPremRptCmdTxt
(
DataSet_CommandText
Language 1033
)
KEY INDEX idx_ID
ON Prem_tmpPremRptCmdTxt_FTCat
WITH CHANGE_TRACKING OFF
This is the part of the query that calls the function to do the scans and updates the report table if it finds any custom object references:
UPDATE tmpPremRptCmdTxt SET [CustomObjectsUsedInCommandText] = ISNULL(CustRptObjs.[CustomObjectsLocated], '')
FROM tmpPremRptCmdTxt
LEFT JOIN (
SELECT
ResultSet.ReportID, ResultSet.DatasetID
, STRING_AGG('[' + CONVERT(VARCHAR(MAX), ResultSet.CustomObjects) + '] (' + ResultSet.ObjectType + ')', '; ') as [CustomObjectsLocated]
FROM (
SELECT
Rpt.ReportID, Rpt.DatasetID, Cob.ObjectType
, dbo.[fnPremFTSearch_v2](MAX(Rpt.ID), Rpt.ReportID, Rpt.DatasetID, Cob.ObjectName) as CustomObjects
FROM tmpPremRptCmdTxt Rpt
OUTER APPLY
(
SELECT DISTINCT ObjectName, ObjectType
FROM tmpPremCustomObjects
) Cob
WHERE ISNULL(Rpt.DataSet_CommandText, '') <> ''
GROUP BY Rpt.ReportName, Rpt.ReportID, Rpt.DatasetID, Cob.ObjectName,ObjectType
HAVING dbo.[fnPremFTSearch_v2](MAX(Rpt.ID), Rpt.ReportID, Rpt.DatasetID, Cob.ObjectName) IS NOT NULL
) ResultSet
GROUP BY ResultSet.ReportID, ResultSet.DatasetID
) CustRptObjs ON CustRptObjs.ReportID = tmpPremRptCmdTxt.ReportID
AND CustRptObjs.DatasetID = tmpPremRptCmdTxt.DatasetID
This is the code of the function that I call to do the scan:
CREATE FUNCTION [dbo].[fnPremFTSearch_v2](@ID INT, @ReportID INT, @DatasetID INT, @ObjectName AS VARCHAR(256))
RETURNS VARCHAR(256)
AS
BEGIN
DECLARE @ReturnText VARCHAR(256) = ''
, @SearchText VARCHAR(256) = CONCAT('"', @ObjectName, '"')
SELECT @ReturnText = @ObjectName
FROM tmpPremRptCmdTxt
WHERE ReportID = @ReportID
AND DatasetID = @DatasetID
AND ID = @ID
AND CONTAINS(DataSet_CommandText, @SearchText)
AND DataSet_CommandText LIKE CONCAT('%',@ObjectName, '%') --Weeds out any near matches...i.e. "EmployeeDetail$" would return if CommandText contained "EmployeeDetail" but not "EmployeeDetail$"
IF (@ReturnText = '')
SET @ReturnText = NULL
RETURN @ReturnText
END
GO
