[BUG] procedure_stats collector fails when trigger has a comment with an " on " in it

[Thallerich]

Component

Lite

Performance Monitor Version

2.3.0

SQL Server Version

SQL Server 2019 CU 30

Windows Version

Windows 11 25H2

Describe the Bug

We have a trigger in our database like this:

-- =============================================
-- Description: Update Current LoadIndex Id on Machine record
-- =============================================
CREATE TRIGGER [MonitorData].[Trigger_Update_Current_LoadIndex]
ON [MonitorData].[LoadIndex]
AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
UPDATE [MonitorData].[Machine]
SET CurrentLoadIndexId = i.Id
FROM INSERTED i
INNER JOIN [MonitorData].[Machine] m ON i.MachineId = m.Id
END

For this trigger the procedure_stats collector fails with error message: SQL Error #537: Invalid length parameter passed to the LEFT or SUBSTRING function.

As far as i can tell this happens because the substring function gets a negative value here due to the comment before the create trigger statement containing an " on ":

SUBSTRING(
st.text,
CHARINDEX(N'CREATE TRIGGER', st.text) + 15,
CHARINDEX(N' ON ', st.text + N' ON ') - CHARINDEX(N'CREATE TRIGGER', st.text) - 15
), N'[', N''), N']', N'')))

Steps to Reproduce

Have the above trigger in a database and connect with PerformanceMonitorLite.

Expected Behavior

should correctly get the text after the create trigger statement

Actual Behavior

fails with error due to an " on " before the create trigger

Error Messages / Log Output

Screenshots

No response

Additional Context

No response

[erikdarlingdata]

Fixed in PR #667 — CHARINDEX for ' ON ' now starts searching after the CREATE TRIGGER position, so comments containing 'on' before the trigger definition no longer cause a negative SUBSTRING length.