DatabaseProcApplicationCreatedLinks
sybsystemprocssp_mon_archive_genSQL  14 déc. 14Defects Propagation Dependencies

1     
2     create or replace procedure sp_mon_archive_genSQL(
3         @montable varchar(30)
4         , @datestamp datetime
5         , @selstmt varchar(1800) output
6         , @fromclause varchar(30) = NULL output
7         , @printsql tinyint = 0
8     ) as
9         begin
10            declare @retval int
11                , @generated_cols varchar(1024)
12                , @filler varchar(10)
13    
14                , @whoami varchar(30)
15                , @indent varchar(32)
16    
17                -- SP/PL/version/EBF # info from @@version
18                , @spnumstr varchar(20)
19                , @plnumstr varchar(20)
20                , @ebfnumstr varchar(20)
21                , @spnum int
22                , @plnum int
23                , @ebfnum int
24    
25                , @sqllen int
26                , @sqlno int
27                , @curpos int
28                , @totallen int
29                , @sqltext varchar(1096)
30    
31            select @whoami = 'sp_mon_archive_genSQL'
32                , @indent = char(10) + space(2 * @@nestlevel)
33    
34            -- Start the SELECT statement. We skimp on white spaces here
35            -- mainly to use as much SQL buffer space as possible. This compromises
36            -- on readability, but we only do this once, and once stable, there
37            -- will be no need to view this generated SQL (hopefully!).
38            --
39            select @selstmt = 'SELECT '
40                , @generated_cols = NULL
41    
42                -- This is used as a placeholder in the generated SQL for the
43                -- current date time. It'll be replaced shortly below.
44                --
45                , @filler = 'XXXXXXXXXX'
46    
47            if (@printsql = 1)
48            begin
49                print "%1!---- Start Trace %2!: ----"
50                    , @indent, @whoami
51            end
52    
53            -- If a previously generated SQL statement was found in the control
54            -- table, retrieve the fragments and replace that directly.
55            -- 
56            if exists (select 1 from tempdb.dbo.mon_config
57                    where monitor = @montable
58                        and confignum = 0)
59            begin
60                -- The sql text might be saved in multiple lines in mon_config.
61                -- We will concatenate it by sqlseqno.
62                declare sqltext_cur cursor for
63                select sqlstmt, sqlseqno, configname
64                from tempdb.dbo.mon_config
65                where monitor = @montable
66                    and confignum = 0
67                order by sqlseqno
68    
69                open sqltext_cur
70    
71                while (1 = 1)
72                begin
73                    fetch sqltext_cur into @sqltext, @sqlno, @fromclause
74                    if (@@sqlstatus != 0)
75                        break
76                    select @selstmt = @selstmt + @sqltext
77                end
78    
79                close sqltext_cur
80    
81                select @retval = @@error
82                if (@retval != 0)
83                    return @retval
84    
85                -- Replace the placeholder with the real date stamp for this
86                -- run.
87                --
88                select @selstmt = str_replace(@selstmt, @filler, @datestamp)
89    
90                if (@printsql = 1)
91                begin
92                    print "%1! Retrieved SQL from control table: %2!"
93                        , @indent, 'tempdb.dbo.mon_config'
94                end
95    
96                goto exit_proc
97            end
98    
99            -- Generate column list for the specific monitoring table.
100           exec @retval = sp_mon_gen_columnlist @montable, @selstmt output
101               , @generated_cols output
102           if (@retval != 0)
103               return @retval
104   
105           -- Pull-out version string related useful info.
106           --
107           exec sp_versioncrack @@version, "SP", @spnumstr out, @spnum out
108           exec sp_versioncrack @@version, "PL", @plnumstr out, @plnum out
109           exec sp_versioncrack @@version, "EBF", @ebfnumstr out, @ebfnum out
110   
111           -- Add the remaining fields explicitly as NULL'able fields.
112           select @generated_cols = @generated_cols
113               + ',ServerName=convert(varchar(30) NULL, @@servername)'
114               + ",ArchiveDate=convert(datetime NULL,"
115               + "'" + @filler + "'" + ")"
116   
117               -- Generated columns to identify server
118               + ",VersionNum=" + convert(varchar, @@version_number)
119               + ",SPNum=" + convert(varchar, @spnum)
120               + ",PLNum=" + convert(varchar, @plnum)
121               + ",EBFNum=" + convert(varchar, @ebfnum)
122   
123           -- Print this, so that in other scripts that reference this
124           -- #temp table we can cut-and-paste this directly and avoid code
125           -- errors.
126           --
127           if (@printsql = 1)
128           begin
129               print "%1! ", @selstmt
130               print "%1! -- '%2!'", @generated_cols, @datestamp
131               print "INTO #%1!", @montable
132               print "%1!", @fromclause
133           end
134   
135           select @selstmt = @selstmt + @generated_cols
136               , @fromclause = "FROM " + 'master.dbo.' + @montable + ' m'
137   
138           -- Save off this generated SQL in the control table, so that we can
139           -- reuse it later, and simply replace the @filler with the new
140           -- @datestamp that we will get on subsequent reuses.
141           --
142           select @sqlno = 0
143           select @curpos = 1
144           select @totallen = char_length(@selstmt)
145           while (@curpos < @sqllen)
146           begin
147               -- Since sqlstmt column in mon_config is defined as 1096, we
148               -- need to check if sql text is longer than 1096. If not, we
149               -- can save it directly. Otherwise, we will save the text
150               -- in multiple lines. By sqlno, we can know the sequence of
151               -- the text in different lines.
152               if ((@totallen - @curpos) < 1096)
153               begin
154                   -- The sql text is shorter than 1096. Save it without
155                   -- split.
156                   select @sqllen = @totallen - @curpos + 1
157               end
158               else
159               begin
160                   -- The remaining characters is more than 1096. So, we
161                   -- can save the first 1096 characters in this time.
162                   select @sqllen = 1096
163               end
164               select @sqltext = substring(@selstmt, @curpos, @sqllen)
165               insert tempdb.dbo.mon_config(monitor, confignum, configval, configname,
166                   enabled, sqlstmt, sqlseqno)
167               select @montable
168                   , 0
169                   , 0
170                   , @fromclause
171                   , @datestamp
172                   , @sqltext
173                   , @sqlno
174   
175               select @curpos = @curpos + @sqllen
176               select @sqlno = @sqlno + 1
177           end
178   
179           select @retval = @@error
180           if (@retval != 0)
181               return @retval
182   
183           -- Before returning to caller, replace the filler substring with
184           -- the real datestamp for this run.
185           --
186           select @selstmt = str_replace(@selstmt, @filler, @datestamp)
187   
188           if (@printsql = 1)
189           begin
190               print "%1! Generated SQL and archive to control table: %2!"
191                   , @indent, 'tempdb.dbo.mon_config'
192           end
193   
194   exit_proc:
195   
196           if (@printsql = 1)
197           begin
198               print "%1!---- End Trace %2! (retval = %3!) ----"
199                   , @indent, @whoami, @retval
200           end
201           return 0
202   
203       end -- }
204   

DEFECTS
 PERR 6 Parsing Error Could not find definition for table tempdb..mon_config 56
 PERR 6 Parsing Error Could not find definition for table tempdb..mon_config 64
 PERR 6 Parsing Error Could not find definition for table tempdb..mon_config 165
 MTYP 4 Assignment type mismatch @sellist: varchar(1500) = varchar(1800) 100
 MTYP 4 Assignment type mismatch @gen_cols: varchar(768) = varchar(1024) 101
 MULT 4 Using literal database 'tempdb' tempdb..mon_config 56
 MULT 4 Using literal database 'tempdb' tempdb..mon_config 64
 MULT 4 Using literal database 'tempdb' tempdb..mon_config 165
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs varchar(30) 57
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs int 58
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs varchar(30) 65
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs int 66
 VRUN 4 Variable is read and not initialized @sqllen 145
 MNER 3 No Error Check should check return value of exec 107
 MNER 3 No Error Check should check return value of exec 108
 MNER 3 No Error Check should check return value of exec 109
 MNER 3 No Error Check should check @@error after insert 165
 MUCO 3 Useless Code Useless Brackets in create proc 2
 MUCO 3 Useless Code Useless Begin-End Pair 9
 MUCO 3 Useless Code Useless Brackets 47
 MUCO 3 Useless Code Useless Brackets 71
 MUCO 3 Useless Code Useless Brackets 74
 MUCO 3 Useless Code Useless Brackets 82
 MUCO 3 Useless Code Useless Brackets 90
 MUCO 3 Useless Code Useless Brackets 102
 MUCO 3 Useless Code Useless Brackets 127
 MUCO 3 Useless Code Useless Brackets 145
 MUCO 3 Useless Code Useless Brackets 152
 MUCO 3 Useless Code Useless Brackets 180
 MUCO 3 Useless Code Useless Brackets 188
 MUCO 3 Useless Code Useless Brackets 196
 MZMB 3 Zombie: use of non-existent object tempdb..mon_config 56
 MZMB 3 Zombie: use of non-existent object tempdb..mon_config 64
 MZMB 3 Zombie: use of non-existent object tempdb..mon_config 165
 VNRD 3 Variable is not read @spnumstr 107
 VNRD 3 Variable is not read @plnumstr 108
 VNRD 3 Variable is not read @ebfnumstr 109
 MTR1 2 Metrics: Comments Ratio Comments: 31% 2
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 16 = 16dec - 2exi + 2 2
 MTR3 2 Metrics: Query Complexity Complexity: 76 2

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_versioncrack  
   calls proc sybsystemprocs..sp_split_string  
calls proc sybsystemprocs..sp_mon_gen_columnlist  
   reads table master..syscolumns (1)  
   reads table master..sysobjects (1)  

CALLERS
called by proc sybsystemprocs..sp_mon_archive_monTable  
   called by proc sybsystemprocs..sp_mon_archive_deadlock  
called by proc sybsystemprocs..sp_monitor_deadlock  
   called by proc sybsystemprocs..sp_monitor