August 26, 2014 at 3:21 am
Looking at a trace table and trying to remove all the "erroneous" bits in a string e.g the declares etc so I can purely get to proc names.
An example string
declare @p2 varchar(10) set @p2=NULL exec sp_proc @Code='TF',@TypeCode=@p2 output select @p2
Ive tried
select top 5000 textdata,substring(textdata,charindex('exec',textdata)+5,charindex('@',textdata)-1)
from trace_table
where TextData like '%sp_%'
and TextData like '%declare%'
And it fails dismally 🙁
Please, words of wisdom before I leap out my ground floor window !!
August 26, 2014 at 4:11 am
simon_s (8/26/2014)
Looking at a trace table and trying to remove all the "erroneous" bits in a string e.g the declares etc so I can purely get to proc names.An example string
declare @p2 varchar(10) set @p2=NULL exec sp_proc @Code='TF',@TypeCode=@p2 output select @p2
Ive tried
select top 5000 textdata,substring(textdata,charindex('exec',textdata)+5,charindex('@',textdata)-1)
from trace_table
where TextData like '%sp_%'
and TextData like '%declare%'
And it fails dismally 🙁
Please, words of wisdom before I leap out my ground floor window !!
Quick thought, go down to the basement:-D and post couple of examples of the strings you are working with.
😎
August 26, 2014 at 4:17 am
sorry not familiar with the phrase "go down to the basement" ?
anyway yes sure this was an example string
declare @p2 varchar(10) set @p2=NULL exec sp_proc @Code='TF',@TypeCode=@p2 output select @p2
thanks simon
August 26, 2014 at 4:42 am
simon_s (8/26/2014)
sorry not familiar with the phrase "go down to the basement" ?
"Go down to the basement / cellar" was just an attempt to mitigate the risk of you throwing your self out the ground floor window:-P
anyway yes sure this was an example string
declare @p2 varchar(10) set @p2=NULL exec sp_proc @Code='TF',@TypeCode=@p2 output select @p2
thanks simon
Here is a code that should get you going
😎
SELECT SUBSTRING(@EXECSTR, CHARINDEX(N'exec ',@EXECSTR,1) + LEN(N'exec '), CHARINDEX(N'@',@EXECSTR,CHARINDEX(N'exec ',@EXECSTR,1)) - (CHARINDEX(N'exec ',@EXECSTR,1) + + LEN(N'exec ')))
Returns
sp_proc
August 26, 2014 at 4:47 am
Eirikur Eiriksson (8/26/2014)
simon_s (8/26/2014)
Looking at a trace table and trying to remove all the "erroneous" bits in a string e.g the declares etc so I can purely get to proc names.An example string
declare @p2 varchar(10) set @p2=NULL exec sp_proc @Code='TF',@TypeCode=@p2 output select @p2
Ive tried
select top 5000 textdata,substring(textdata,charindex('exec',textdata)+5,charindex('@',textdata)-1)
from trace_table
where TextData like '%sp_%'
and TextData like '%declare%'
And it fails dismally 🙁
Please, words of wisdom before I leap out my ground floor window !!
Quick thought, go down to the basement:-D and post couple of examples of the strings you are working with.
😎
What Eirikur meant was, you need to share us some sample Data from you trace table and your desire output. Check the following How to post data/code on a forum to get the best help[/url].
August 26, 2014 at 2:22 pm
Not sure if you want to get the exact proc name being executed or see it and its parameters. Something like this should help you get started, although it undoubtedly needs additional tweaks:
DECLARE @patindex varchar(30)
SET @patindex = '%[' + SPACE(1) + CHAR(9) + CHAR(10) + CHAR(13) + ';]exec[' + SPACE(1) + CHAR(9) + CHAR(10) + CHAR(13) + ';]%'
select top (5000) exec_start_byte, SUBSTRING(textdata, exec_start_byte, 200) AS exec_string
from (
SELECT 'declare @p2 varchar(10) set @p2=NULL exec sp_proc @Code=''TF'',@TypeCode=@p2 output select @p2' AS textdata
) AS trace_table_sample_data
cross apply (
SELECT CASE WHEN PATINDEX(@patindex, textdata) = 0 THEN 0 ELSE PATINDEX(@patindex, textdata) + 1 END AS exec_start_byte
) as assign_alias
where TextData like '%sp_%'
and TextData like '%declare%'
and exec_start_byte > 0
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 26, 2014 at 8:42 pm
Hi
Here's another variation that should work. It uses a couple of stuffs rather than a substring
SELECT TOP 5000 TextData,
STUFF( --Replace beginning with empty string
-- Check for @ after exec
CASE WHEN CHARINDEX('@',TextData, CHARINDEX('exec ',TextData)) > 0 THEN
STUFF( --Replace end with empty string
TextData,
CHARINDEX('@',TextData, CHARINDEX('exec ',TextData)) - 1
,8000
,''
)
ELSE --No need to replace end
TextData
END
,1
,CHARINDEX('exec ',TextData) + 4
,''
) StoredProc
FROM trace_table
WHERE TextData like '%sp_%' AND
TextData like '%declare%' AND
TextData like '%exec %'
--ORDER BY Something
August 26, 2014 at 10:53 pm
Further on my previous snip, here is a more complete code block with some sample data
😎
USE tempdb;
GO
DECLARE @NUM_SAMPLES INT = 100;
DECLARE @SAMPLE_STR NVARCHAR(MAX) = N'declare @p? varchar(10) set @p?=NULL exec sp_proc_? @Code=''TF'',@TypeCode=@p? output select @p?';
DECLARE @LOG_TABLE TABLE
(
LOG_ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL
,EXEC_STRING NVARCHAR(MAX) NOT NULL
);
;WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
,NUMS(N) AS (SELECT TOP(@NUM_SAMPLES) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2, T T3 ,T T4, T T5, T T6, T T7)
INSERT INTO @LOG_TABLE (EXEC_STRING)
SELECT
REPLACE(@SAMPLE_STR,N'?',CONVERT(NVARCHAR(12),NM.N, 1))
FROM NUMS NM
SELECT
LT.LOG_ID
,SUBSTRING(LT.EXEC_STRING,
CHARINDEX(N'exec ',LT.EXEC_STRING,1) + LEN(N'exec '),
CHARINDEX(N'@',LT.EXEC_STRING,
CHARINDEX(N'exec ',LT.EXEC_STRING,1)) - (CHARINDEX(N'exec ',LT.EXEC_STRING,1) + LEN(N'exec '))
) AS PROC_NAME
,LT.EXEC_STRING
FROM @LOG_TABLE LT;
Top 10 Results
LOG_ID PROC_NAME EXEC_STRING
------- ------------ -----------------------------------------------------------------------------------------------------
1 sp_proc_1 declare @p1 varchar(10) set @p1=NULL exec sp_proc_1 @Code='TF',@TypeCode=@p1 output select @p1
2 sp_proc_2 declare @p2 varchar(10) set @p2=NULL exec sp_proc_2 @Code='TF',@TypeCode=@p2 output select @p2
3 sp_proc_3 declare @p3 varchar(10) set @p3=NULL exec sp_proc_3 @Code='TF',@TypeCode=@p3 output select @p3
4 sp_proc_4 declare @p4 varchar(10) set @p4=NULL exec sp_proc_4 @Code='TF',@TypeCode=@p4 output select @p4
5 sp_proc_5 declare @p5 varchar(10) set @p5=NULL exec sp_proc_5 @Code='TF',@TypeCode=@p5 output select @p5
6 sp_proc_6 declare @p6 varchar(10) set @p6=NULL exec sp_proc_6 @Code='TF',@TypeCode=@p6 output select @p6
7 sp_proc_7 declare @p7 varchar(10) set @p7=NULL exec sp_proc_7 @Code='TF',@TypeCode=@p7 output select @p7
8 sp_proc_8 declare @p8 varchar(10) set @p8=NULL exec sp_proc_8 @Code='TF',@TypeCode=@p8 output select @p8
9 sp_proc_9 declare @p9 varchar(10) set @p9=NULL exec sp_proc_9 @Code='TF',@TypeCode=@p9 output select @p9
10 sp_proc_10 declare @p10 varchar(10) set @p10=NULL exec sp_proc_10 @Code='TF',@TypeCode=@p10 output select @p10
August 27, 2014 at 3:55 am
thank you all for your replies and examples... really appreciated
~simon
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply