| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_mon_archive_data | | 31 Aug 14 | Defects Dependencies |
1 2 create procedure sp_mon_archive_data( 3 @archivename varchar(40) 4 , @selstmt varchar(1800) 5 , @fromclause varchar(30) 6 , @whereclause varchar(255) = NULL 7 , @uniqueindex_name varchar(30) = NULL 8 , @datestamp datetime 9 , @created int output 10 , @trace int 11 , @unique_col1 varchar(30) = NULL 12 , @unique_col2 varchar(30) = NULL 13 , @unique_col3 varchar(30) = NULL 14 , @unique_col4 varchar(30) = NULL 15 , @unique_col5 varchar(30) = NULL 16 ) as 17 begin 18 declare @whoami varchar(30) 19 , @indent varchar(32) 20 , @sqlstmt varchar(1800) 21 , @msg varchar(80) 22 , @do_freeze_mda tinyint 23 , @retval int 24 , @mda_retval int 25 , @tmp_retval int 26 , @num_rows int 27 , @crt_newarchive tinyint 28 29 select @whoami = 'sp_mon_archive_data' 30 , @indent = char(10) + space(2 * @@nestlevel) 31 32 if (@trace = 1) 33 begin 34 print "%1!---- Start Trace %2!: ----" 35 , @indent, @whoami 36 end 37 if (object_id(@archivename) IS NULL) 38 begin -- { 39 -- Create a new table using SELECT INTO. 40 -- 41 select @sqlstmt = @selstmt 42 + " INTO " + @archivename 43 + ' ' 44 + @fromclause 45 + ' ' 46 + @whereclause 47 48 , @crt_newarchive = 1 49 , @do_freeze_mda = 1 50 end -- } 51 else 52 begin -- { 53 54 -- Re-create an INSERT...SELECT SQL statement adding WHERE 55 -- clauses for duplicate elimination, using the columns 56 -- specified for uniqueness. 57 -- 58 select @sqlstmt = 'INSERT ' + @archivename 59 + ' ' 60 + @selstmt 61 + ' ' 62 + @fromclause 63 + ' ' 64 + @whereclause 65 66 -- Archive already exists. Just insert into it. 67 , @crt_newarchive = 0 68 69 -- We need to freeze MDA for insert-select, also. 70 -- 71 , @do_freeze_mda = 1 72 73 end -- } 74 75 -- If we are creating a new archive, here is where the SQL 76 -- to extract data from monDeadLock will get executed. Freeze 77 -- it, and unfreeze it after the SQL executes. 78 -- 79 if (@do_freeze_mda = 1) 80 begin 81 select @mda_retval = mdaconfig('freeze', 'begin') 82 end 83 84 select @num_rows = 0 85 exec @retval = sp_exec_SQL @sqlstmt 86 , "sp_mon_archive_data-archive" 87 , @num_rows output 88 , @trace 89 if (@do_freeze_mda = 1) 90 begin 91 select @mda_retval = mdaconfig('freeze', 'end') 92 end 93 94 -- The data was correctly archived either via SELECT-INTO or 95 -- INSERT-SELECT. Report on that. 96 -- 97 if (@retval = 0) 98 begin 99 -- Reset archive name to be full name, incl dbname. 100 -- [Reuse variable for message reporting.] 101 -- 102 select @sqlstmt = db_name() + ".dbo." + @archivename 103 if (@crt_newarchive = 1) 104 begin 105 exec sp_getmessage 19489, @msg output 106 print @msg, @sqlstmt 107 end 108 exec sp_getmessage 19490, @msg output 109 print @msg, @num_rows, @sqlstmt, @datestamp 110 111 end 112 113 -- Start building the CREATE INDEX sql and then exec it. Unique 114 -- index used for duplicate elimination during future inserts. 115 -- 116 if ((@retval = 0) and (@crt_newarchive = 1)) 117 begin 118 select @sqlstmt = "CREATE UNIQUE INDEX " 119 + @uniqueindex_name 120 + " ON " 121 + @archivename 122 + "(" 123 124 if (@unique_col1 IS NOT NULL) 125 begin 126 select @sqlstmt = @sqlstmt 127 + @unique_col1 128 end 129 130 if (@unique_col2 IS NOT NULL) 131 begin 132 select @sqlstmt = @sqlstmt 133 + "," 134 + @unique_col2 135 end 136 137 if (@unique_col3 IS NOT NULL) 138 begin 139 select @sqlstmt = @sqlstmt 140 + "," 141 + @unique_col3 142 end 143 144 if (@unique_col4 IS NOT NULL) 145 begin 146 select @sqlstmt = @sqlstmt 147 + "," 148 + @unique_col4 149 end 150 151 if (@unique_col5 IS NOT NULL) 152 begin 153 select @sqlstmt = @sqlstmt 154 + "," 155 + @unique_col5 156 end 157 select @sqlstmt = @sqlstmt 158 + ")" 159 + " WITH IGNORE_DUP_KEY" 160 161 exec @retval = sp_exec_SQL @sqlstmt 162 , 'archive_data-crt-unique-index' 163 , NULL, @trace 164 end 165 166 -- Deem successful creation of table only if index was also 167 -- successfully built. 168 -- 169 if (@retval = 1) 170 select @created = 1 171 172 if (@trace = 1) 173 begin 174 print "%1!---- End Trace %2! (retval=%3!) ----" 175 , @indent, @whoami, @retval 176 end 177 178 return @retval 179 end -- } 180
| DEPENDENCIES |
| PROCS AND TABLES USED calls proc sybsystemprocs..sp_exec_SQL calls proc sybsystemprocs..sp_getmessage  reads table sybsystemprocs..sysusermessages calls proc sybsystemprocs..sp_validlang reads table master..syslanguages (1)  reads table master..sysmessages (1) reads table master..syslanguages (1) CALLERS called by proc sybsystemprocs..sp_mon_archive_monTable called by proc sybsystemprocs..sp_mon_archive_deadlock |
