Database	Proc	Application	Created	Links
sybsystemprocs	sp_helpapptrace		31 Aug 14	Defects Dependencies

1     
2     
3     /*
4     ** Messages for sp_helpapptrace
5     **
6     ** 17260, "Can't run %1! from within a transaction."
7     **
8     */
9     
10    create procedure sp_helpapptrace as
11    
12        declare @low int
13        declare @high int
14        declare @spidlow int
15        declare @spidhigh int
16        declare @dummy int
17        declare @sa_role int /* has sa role */
18        declare @sso_role int /* has sso role */
19    
20        /* Dont allow sp_helpapptrace to run with in a transaction */
21        if @@trancount > 0
22        begin
23            /*
24            ** 17260, "Can't run %1! from within a transaction."
25            */
26            raiserror 17260, "sp_helpapptrace"
27            return (1)
28        end
29        else
30        begin
31            set transaction isolation level 1
32            set chained off
33        end
34    
35        select @sa_role = charindex("sa_role", show_role()),
36            @sso_role = charindex("sso_role", show_role())
37    
38        if ((@sa_role = 0) and (@sso_role = 0))
39        begin
40            select @dummy = proc_role("sa_role")
41            select @dummy = proc_role("sso_role")
42            return (1)
43        end
44        else
45        begin
46            if (@sa_role > 0)
47            begin
48                select @dummy = proc_role("sa_role")
49            end
50            if (@sso_role > 0)
51            begin
52                select @dummy = proc_role("sso_role")
53            end
54        end
55    
56        select @low = @@minsuid, @high = @@maxsuid,
57            @spidlow = @@minspid, @spidhigh = @@maxspid
58    
59        /*
60        ** Find all the sessions currently being traced.
61        */
62    
63        select traced = spid
64        into #table1
65        from master..sysprocesses
66        where suid >= @low and suid <= @high
67            and spid >= @spidlow and spid <= @spidhigh
68            and (convert(int, pssinfo(spid, "tracefile_is_set")) != 0)
69    
70        /*
71        ** Find all the spids and the spids that they are tracing. 
72        ** Note that if the tracer spid has exited then there will be no entry
73        ** for that spid.
74        */
75    
76        select tracer = spid, tracee = convert(int, pssinfo(spid, "trace_spid"))
77        into #table2
78        from master..sysprocesses
79        where suid >= @low and suid <= @high
80            and spid >= @spidlow and spid <= @spidhigh
81    
82        /*
83        ** Now combine the above two tables using LEFT OUTER JOIN. This will
84        ** ensure that there is an entry for even those spids whose tracers
85        ** have exited. Additionally also get the paths of the tracefile for
86        ** traced spids.
87        */
88    
89        select traced_spid = convert(char(10), traced),
90            tracer_spid = isnull(convert(char(10), tracer), 'exited'),
91            trace_file = pssinfo(traced, "trace_fname")
92        into #helpapptrace_result
93        from #table1, #table2
94        where traced *= tracee
95    
96        exec sp_autoformat @fulltabname = #helpapptrace_result,
97            @selectlist = "traced_spid, tracer_spid, trace_file",
98            @orderby = "order by traced_spid"
99    
100       drop table #table1
101       drop table #table2
102       drop table #helpapptrace_result
103   
104       return (0)
105



exec sp_procxmode 'sp_helpapptrace', 'AnyMode'
go

Grant Execute on sp_helpapptrace to public
go

DEFECTS
MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname	96
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	67
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	80
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	94
TNOI 4 Table with no index master..sysprocesses	master..sysprocesses
MGTP 3 Grant to public master..sysprocesses
MGTP 3 Grant to public sybsystemprocs..sp_helpapptrace
MNER 3 No Error Check should check @@error after select into	63
MNER 3 No Error Check should check @@error after select into	76
MNER 3 No Error Check should check @@error after select into	89
MNER 3 No Error Check should check return value of exec	96
MUCO 3 Useless Code Useless Brackets	27
MUCO 3 Useless Code Useless Brackets	38
MUCO 3 Useless Code Useless Brackets	42
MUCO 3 Useless Code Useless Brackets	46
MUCO 3 Useless Code Useless Brackets	50
MUCO 3 Useless Code Useless Brackets	104
QISO 3 Set isolation level	31
QJWT 3 Join or Sarg Without Index on temp table	94
QNAO 3 Not using ANSI Outer Join	93
QNUA 3 Should use Alias: Column traced should use alias #table1	89
QNUA 3 Should use Alias: Column tracer should use alias #table2	90
QNUA 3 Should use Alias: Column traced should use alias #table1	91
QNUA 3 Should use Alias: Table #table1	93
QNUA 3 Should use Alias: Table #table2	93
QNUA 3 Should use Alias: Column traced should use alias #table1	94
QNUA 3 Should use Alias: Column tracee should use alias #table2	94
VNRD 3 Variable is not read @dummy	52
MTR1 2 Metrics: Comments Ratio Comments: 26%	10
MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 11 = 12dec - 3exi + 2	10
MTR3 2 Metrics: Query Complexity Complexity: 53	10

DEPENDENCIES

PROCS AND TABLES USED
read_writes table tempdb..#table2 (1)
reads table master..sysprocesses (1)

calls proc sybsystemprocs..sp_autoformat

reads table tempdb..syscolumns (1)

read_writes table tempdb..#colinfo_af (1)
reads table master..syscolumns (1)

reads table tempdb..systypes (1)

reads table master..systypes (1)

calls proc sybsystemprocs..sp_namecrack

read_writes table tempdb..#table1 (1)
writes table tempdb..#helpapptrace_result (1)