Assistance with code used to identify currently executing SQL statements

Question

Assistance with code used to identify currently executing SQL statements

Adam Bean

One Orange Chip

Points: 26631
More actions
April 10, 2014 at 6:24 am

#396577

Hey all,
Having an issue with my view to show all current sessions, with a "Invalid length parameter passed to the LEFT or SUBSTRING function." I've never figured out what particular queries cause it; however I have narrowed down exactly where it fails:
ELSE LTRIM(SUBSTRING(h.[text], r.[statement_start_offset] / 2 + 1
I was able to fix the matter at hand by changing 2 + 1, to 2 + 2; however now I'm finding those random random queries that were throwing the error, now trim the first character off.
What if any resolution exists to alleviate certain queries throwing the invalid length error as obviously using 2+2 for the start in SUBSTRING isn't an appropriate fix.
SELECT
LTRIM(SUBSTRING(h.[text], r.[statement_start_offset] / 2 + 1,
((
CASE
WHEN r.[statement_end_offset] = -1THEN DATALENGTH(h.[text])
ELSE r.[statement_end_offset]
END
) - r.[statement_start_offset]) / 2))
FROM [sys].[dm_exec_connections] c
LEFT JOIN [sys].[dm_exec_requests] r
ON c.[session_id] = r.[session_id]
OUTER APPLY [sys].[dm_exec_sql_text](c.[most_recent_sql_handle]) h
Thank you!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

g.britton SSChampion Points: 13863 More actions · Answer 1

Try your query on some test data rather than live data. Experiment with various lengths of input. It smells like and even/odd problem, but without real data to look at its not obvious.

Gerald Britton, Pluralsight courses

Adam Bean One Orange Chip Points: 26631 More actions · Answer 2

Well, removing the +1 does fix it; however then there is a leading white space character.

This code is widely used online in a multitude of threads/blogs, etc.

Adam Bean One Orange Chip Points: 26631 More actions · Answer 3

Well, guess not. Regardless of the + 1 or + 2, I just found an instance where it will still randomly blow up.

It may even be the ending ) - r.[statement_start_offset]) / 2).

Cody Konior SSCarpal Tunnel Points: 4827 More actions · Answer 4

If you run the code regularly, and it fails from time to time, then wrap it in a try/catch block, and in the catch block have it insert records from the few un-manipulated fields into a holding table, then you can re-analyse it at your leisure and find out exactly what they are!

Adam Bean One Orange Chip Points: 26631 More actions · Answer 5

Cody K (4/10/2014)
If you run the code regularly, and it fails from time to time, then wrap it in a try/catch block, and in the catch block have it insert records from the few un-manipulated fields into a holding table, then you can re-analyse it at your leisure and find out exactly what they are!

Yeah the try/catch won't work as a view (I do have a proc version of this too), but a good thought about attempting to capture the data to actually find strings that break.