Database | Proc | Application | Created | Links |
sybsystemprocs | sp_mon_archive_genSQL ![]() | ![]() | 31 Aug 14 | Defects 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
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 ![]() |