Trying to fetch procedure name

Question

Post reply

Trying to fetch procedure name

Roust_m

SSCoach

Points: 17410
More actions
July 16, 2003 at 4:57 am

#160526

Hi!
Trying to fetch procedure name (with no parameters) from TextData field from trace results. The TextData field looks like this:
TextData
exec [mbsII]..sp_procedure_params_rowset N'UNIT$OperationAccess', 1, NULL, NULL
exec sp_repldone NULL, 0x0000A2CD0001D7A80003, -1, 266
exec sp_unprepare 10
exec sp_replcounters N'legacy'
exec sp_replcmds 800, 0, -1
use the following query to do this:
SELECT SUBSTRING(TextData, 6,CHARINDEX(' ', TextData, 6))
-- take the substring from 6th character till the first space after 6th character
FROM [trace].[dbo].[trial_trace]
where TextData like 'exec %'
Get this (it finds the second space...):
[mbsII]..sp_procedure_params_rowset N'UNI
sp_repldone NULL,
sp_unprepare 10
sp_replcounters N'leg
sp_replcmds 800,
Am I doing something wrong? Is there another way to do this?

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

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

paul_richardson SSCarpal Tunnel Points: 4594 More actions · Answer 1

hi,

the select should be :-

SELECT SUBSTRING(col1, 6, (CHARINDEX(' ', col1, 6))-6)

-- take the substring from 6th character till the first space after 6th character

FROM pgr_profiler

where col1 like 'exec %'

this is because charindex returns the position of the value in the string as a whole, and doesn't take into account the starting position of 6. so take off 6 as above and it works a treat

Paul

NPeeters SSChampion Points: 12536 More actions · Answer 2

SUBSTRING takes the length as a third parameter. CHARINDEX returns the index of the first occurence.

So you should account for the skipping the first 6 characters.

SUBSTRING(TextData,

          6,

          CHARINDEX(' ', TextData, 6) - 6)

Roust_m SSCoach Points: 17410 More actions · Answer 3

It works fine, but on the 20th line, for:

exec sp_unprepare 12

It returns this:

Server: Msg 536, Level 16, State 3, Line 1

Invalid length parameter passed to the substring function. 🙁

NPeeters SSChampion Points: 12536 More actions · Answer 4

Are you sure it is that line? I can imagine the solution erroring out when there is no space after the 'EXEC_' stuff.

In that case, CHARINDEX returns zero, so you want a negative length. This is not allowed in SUBSTRING.

You can account for that using CASE as in

SUBSTRING(TextData,

          6,

          CASE 

            WHEN CHARINDEX(' ', TextData, 6) < 7

                 THEN LEN(TextData) - 6

            ELSE CHARINDEX(' ', TextData, 6) - 6

            END)

Roust_m SSCoach Points: 17410 More actions · Answer 5

Here it is:

Server: Msg 8116, Level 16, State 2, Line 1

Argument data type ntext is invalid for argument 1 of len function.

🙁

NPeeters SSChampion Points: 12536 More actions · Answer 6

Guilty as charged , didn't test that. Textdata is of course no character field.

You should use DATALENGTH in stead of LEN. Don't know if you have to account for double byte characters?