Database | Proc | Application | Created | Links |
sybsystemprocs | sp_version ![]() | ![]() | 31 Aug 14 | Defects Dependencies |
1 2 /* 3 ** Generated by spgenmsgs.pl on Thu Sep 16 22:53:10 2004 4 */ 5 /* 6 ** raiserror Messages for version [Total 6] 7 ** 8 ** 18524, "%1!: Permission denied. This operation requires System Administrator (sa_role) role." 9 ** 19194, "Argument '%1!' is either invalid or non-unique. Valid arguments are: %2!" 10 ** 19378, "Delete from the table %1! affected %2! rows but expected %3! rows to be deleted. Command aborted." 11 ** 19379, "Update of the table %1! affected %2! rows but expected %3! rows to be updated. Command aborted." 12 ** 19380, "Error in accessing the table %1!." 13 ** 19381, "Invalid argument to %1!. It requires a non-null value." 14 */ 15 /* 16 ** sp_getmessage Messages for version [Total 0] 17 */ 18 /* 19 ** End spgenmsgs.pl output. 20 */ 21 /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */ 22 /* 5.0 1.0 09/10/04 sproc/src/version */ 23 24 /* 25 ** This procedure SP_VERSION is used to find the 26 ** version of the install scripts. In addition to 27 ** this it also gives the details of the successfull 28 ** installation such as begining and ending Date with 29 ** Time of the installation. 30 ** 31 ** This procedure uses the master.dbo.sysattributes 32 ** to store all the required information. The 33 ** columns used for storing the information is 34 ** as follows 35 ** 36 ** Column Name Information stored 37 ** ------------ ------------------ 38 ** object_cinfo Script file name 39 ** char_value Version String 40 ** comments Date and Time info. 41 ** int_value Code value 42 ** (used internally) 43 ** 44 ** In sysattributes this procedure will access the rows 45 ** defined under the class 23. 46 ** 47 ** Usage: 48 ** ------ 49 ** sp_version [@Scriptfile [, @verbose [, @version [, @code ] ] ] ] 50 ** 51 ** Returns: 52 ** 0 - Successful execution. 53 ** 1 - Invalid options. 54 ** 2 - Insert/Update failures. 55 ** 56 */ 57 58 create procedure sp_version 59 @script_file varchar(255) = NULL, 60 @verbose varchar(3) = NULL, 61 @version varchar(255) = NULL, 62 @code varchar(5) = NULL 63 as 64 65 declare @class int 66 , @check_exist int 67 , @date varchar(150) 68 , @start_date varchar(150) 69 , @end_date varchar(150) 70 , @featurecode varchar(2) 71 , @attr_code int 72 , @loc_error int 73 , @loc_rowcount int 74 75 select @class = 23 76 , @check_exist = 0 77 , @start_date = NULL 78 , @end_date = NULL 79 , @featurecode = 'sv' 80 , @attr_code = 2 -- randomly choosen for this feature 81 82 /* 83 ** If code = NULL means it is in reportin mode. 84 */ 85 86 if (@code IS NULL) 87 begin --{ 88 /* 89 ** Check whether the information is for 90 ** a specific install script. If so return 91 ** the required information. Otherwise return 92 ** all the rows with class = 23. 93 */ 94 if (@version IS NULL) 95 begin --{ 96 select object_cinfo as Script, char_value as Version, 97 comments as "Start_End_Date", 98 case int_value 99 when 0 then 'Incomplete' 100 else 'Complete' 101 end as "Status" 102 into #sysattributes 103 from master.dbo.sysattributes 104 where class = @class and object_type = @featurecode 105 and attribute = @attr_code 106 and (@script_file IS NULL 107 or object_cinfo like @script_file) 108 109 /* 110 ** If verbose option is set then print all the information 111 ** including the date and time information. 112 */ 113 114 if (@verbose IS NULL) 115 begin 116 exec sp_autoformat #sysattributes, 117 "Script, Version, Status", null, 118 "ORDER BY 1" 119 end 120 else if (@verbose = 'all') 121 begin 122 exec sp_autoformat #sysattributes, 123 "Script, Version, Status, 124 'Start/End Date' = Start_End_Date", 125 null, "ORDER BY 1" 126 end 127 else 128 begin 129 raiserror 19194, "verbose", "all" 130 return (1) 131 end 132 end --} 133 else 134 begin --{ 135 /* 136 ** version is non-null means it is updating / insert mode 137 ** raise appropriate error messages for script file and 138 ** code value. 139 */ 140 if (@script_file IS NULL) 141 raiserror 19381, "script_file" 142 raiserror 19194, 'code', "'start', 'end'." 143 return (1) 144 end --} 145 return (0) 146 end --} 147 else if (@version IS NULL or @script_file IS NULL) 148 begin 149 /* 150 ** If code is not NULL then check if script_file or version is NULL. 151 ** If any of them are NULL then give an appropriate error message. 152 */ 153 if (@version IS NULL) 154 raiserror 19381, "version" 155 if (@script_file IS NULL) 156 raiserror 19381, "script_file" 157 if (@code != 'start' and @code != 'end') 158 raiserror 19194, 'code', "'start', 'end'" 159 return (1) 160 end 161 else 162 begin 163 /* 164 ** If all the above conditions are satisfied and code is not NULL 165 ** then we need to insert / update the version string. To do this 166 ** check whether the use has 'sa_role'. 167 */ 168 if (proc_role("sa_role") = 0) 169 begin 170 raiserror 18524, "sp_version" 171 return (1) 172 end 173 end 174 175 if (@code = 'start') 176 begin --{ 177 /* 178 ** code = 'start' means the begining of the installation (install 179 ** script). Before adding a new row to sysattributes check 180 ** for the previous rows and take appropriate action. 181 */ 182 183 begin tran update_version_string 184 185 delete from master.dbo.sysattributes 186 where class = @class and object_type = @featurecode 187 and attribute = @attr_code 188 and object_cinfo = @script_file 189 190 select @loc_error = @@error, @loc_rowcount = @@rowcount 191 192 if (@loc_error != 0) --{ 193 begin 194 raiserror 19380, "sysattributes" 195 rollback update_version_string 196 return (2) 197 end --} 198 199 if (@loc_rowcount > 1) --{ 200 begin 201 raiserror 19378, "sysattributes", @@rowcount, "1" 202 rollback update_version_string 203 return (2) 204 end --} 205 206 insert into master.dbo.sysattributes(class, object_type, 207 attribute, object_cinfo, char_value, int_value, comments) 208 values (@class, @featurecode, @attr_code, @script_file, 209 @version, 0, "[Started=" + convert(varchar, getdate()) + "]") 210 211 if (@@error != 0) 212 begin 213 raiserror 19380, "sysattributes" 214 rollback update_version_string 215 return (2) 216 end 217 218 commit tran update_version_string 219 return (0) 220 end --} 221 else if (@code = 'end') 222 begin --{ 223 /* 224 ** @code = 'end' represents the successfull installation 225 ** of the script. Change int_value to reflect this 226 ** Before updating check whether the script is 227 ** is registered for the installation. If so update 228 ** row for the script file with int_value = 0. 229 ** Otherwise raise an error. 230 */ 231 232 begin tran complete_version_string 233 234 /* 235 ** The script is registered at the begining of the 236 ** Installtaion. 237 */ 238 239 select @date = convert(varchar, getdate()) 240 241 select @start_date = comments 242 from master.dbo.sysattributes 243 where class = @class and attribute = @attr_code 244 and object_type = @featurecode 245 and object_cinfo = @script_file 246 247 248 if (@start_date IS NOT NULL) --{ 249 begin 250 select @end_date = @start_date 251 + '-[Completed=' + @date + ']' 252 253 update master.dbo.sysattributes 254 set int_value = 1, comments = @end_date 255 where class = @class and attribute = @attr_code 256 and object_type = @featurecode 257 and object_cinfo = @script_file 258 259 select @loc_error = @@error, @loc_rowcount = @@rowcount 260 261 if (@loc_error != 0) --{ 262 begin 263 raiserror 19380, "sysattributes" 264 rollback complete_version_string 265 return (2) 266 end --} 267 268 if (@loc_rowcount > 1) --{ 269 begin 270 raiserror 19379, "sysattributes", @@rowcount, "1" 271 rollback complete_version_string 272 return (2) 273 end --} 274 end --} 275 else 276 begin --{ 277 278 select @end_date = "[Completed=" + @date + "]" 279 280 insert into master.dbo.sysattributes 281 (class, object_type, attribute, object_cinfo, char_value, 282 int_value, comments) 283 values (@class, @featurecode, @attr_code, @script_file, 284 @version, 1, @end_date) 285 286 if (@@error != 0) --{ 287 begin 288 raiserror 19380, "sysattributes" 289 rollback complete_version_string 290 return (2) 291 end --} 292 293 end --} 294 295 commit tran complete_version_string 296 return (0) 297 end --} 298 else 299 begin 300 raiserror 19194, 'code', "'start', 'end'" 301 return (1) 302 end 303
exec sp_procxmode 'sp_version', 'AnyMode' go Grant Execute on sp_version to public go
DEFECTS | |
![]() | 183 |
![]() | 218 |
![]() | 232 |
![]() | 295 |
![]() | master..sysattributes |
![]() | 208 |
![]() | 208 |
![]() | 241 |
![]() | 283 |
![]() | 283 |
![]() | 116 |
![]() | 122 |
![]() | 104 |
![]() | 104 |
![]() | 105 |
![]() | 105 |
![]() | 186 |
![]() | 186 |
![]() | 187 |
![]() | 187 |
![]() | 243 |
![]() | 243 |
![]() | 255 |
![]() | 255 |
![]() | |
![]() | |
![]() | 96 |
![]() | 116 |
![]() | 122 |
![]() | 86 |
![]() | 94 |
![]() | 114 |
![]() | 120 |
![]() | 130 |
![]() | 140 |
![]() | 143 |
![]() | 145 |
![]() | 147 |
![]() | 153 |
![]() | 155 |
![]() | 157 |
![]() | 159 |
![]() | 168 |
![]() | 171 |
![]() | 175 |
![]() | 192 |
![]() | 196 |
![]() | 199 |
![]() | 203 |
![]() | 211 |
![]() | 215 |
![]() | 219 |
![]() | 221 |
![]() | 248 |
![]() | 261 |
![]() | 265 |
![]() | 268 |
![]() | 272 |
![]() | 286 |
![]() | 290 |
![]() | 296 |
![]() | 301 |
![]() | 241 |
![]() | 96 |
![]() | 206 |
![]() | 281 |
![]() | 106 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {object_type, object_cinfo, attribute, class} | 104 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {object_type, object_cinfo, attribute, class} | 186 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {object_type, object_cinfo, attribute, class} | 243 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {object_type, object_cinfo, attribute, class} | 255 |
![]() | 76 |
![]() | 58 |
![]() | 58 |
![]() | 58 |
DEPENDENCIES |
PROCS AND TABLES USED read_writes table master..sysattributes (1) ![]() calls proc sybsystemprocs..sp_autoformat ![]() calls proc sybsystemprocs..sp_namecrack ![]() reads table tempdb..systypes (1) ![]() reads table tempdb..syscolumns (1) ![]() reads table master..systypes (1) ![]() calls proc sybsystemprocs..sp_autoformat ![]() read_writes table tempdb..#colinfo_af (1) reads table master..syscolumns (1) ![]() writes table tempdb..#sysattributes (1) |