DatabaseProcApplicationCreatedLinks
sybsystemprocssp_mon_archive_genSQL  31 Aug 14Defects Dependencies

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

DEFECTS
 PERR 6 Parsing Error Could not find definition for table tempdb..mon_config 54
 PERR 6 Parsing Error Could not find definition for table tempdb..mon_config 62
 PERR 6 Parsing Error Could not find definition for table tempdb..mon_config 161
 MTYP 4 Assignment type mismatch @sellist: varchar(1500) = varchar(1800) 98
 MTYP 4 Assignment type mismatch @gen_cols: varchar(768) = varchar(1024) 99
 MULT 4 Using literal database 'tempdb' tempdb..mon_config 54
 MULT 4 Using literal database 'tempdb' tempdb..mon_config 62
 MULT 4 Using literal database 'tempdb' tempdb..mon_config 161
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs varchar(30) 55
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs int 56
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs varchar(30) 63
 QTYP 4 Comparison type mismatch Comparison type mismatch: other vs int 64
 VRUN 4 Variable is read and not initialized @sqllen 141
 MNER 3 No Error Check should check return value of exec 105
 MNER 3 No Error Check should check return value of exec 106
 MNER 3 No Error Check should check @@error after insert 161
 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 45
 MUCO 3 Useless Code Useless Brackets 69
 MUCO 3 Useless Code Useless Brackets 72
 MUCO 3 Useless Code Useless Brackets 80
 MUCO 3 Useless Code Useless Brackets 88
 MUCO 3 Useless Code Useless Brackets 100
 MUCO 3 Useless Code Useless Brackets 123
 MUCO 3 Useless Code Useless Brackets 141
 MUCO 3 Useless Code Useless Brackets 148
 MUCO 3 Useless Code Useless Brackets 176
 MUCO 3 Useless Code Useless Brackets 184
 MUCO 3 Useless Code Useless Brackets 192
 MZMB 3 Zombie: use of non-existent object tempdb..mon_config 54
 MZMB 3 Zombie: use of non-existent object tempdb..mon_config 62
 MZMB 3 Zombie: use of non-existent object tempdb..mon_config 161
 VNRD 3 Variable is not read @esdnumstr 105
 VNRD 3 Variable is not read @ebfnumstr 106
 MTR1 2 Metrics: Comments Ratio Comments: 31% 2
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 15 = 15dec - 2exi + 2 2
 MTR3 2 Metrics: Query Complexity Complexity: 75 2

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

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