DatabaseProcApplicationCreatedLinks
sybsystemprocssp_version  14 déc. 14Defects Propagation 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 or replace 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            , @status int
75            , @dbname varchar(255)
76            , @dummy int
77            , @gp_enabled int
78    
79        select @class = 23
80            , @check_exist = 0
81            , @start_date = NULL
82            , @end_date = NULL
83            , @featurecode = 'sv'
84            , @attr_code = 2 -- randomly choosen for this feature
85    
86        /*
87        ** If code = NULL means it is in reportin mode.
88        */
89    
90        if (@code IS NULL)
91        begin --{
92            /*
93            ** Check whether the information is for
94            ** a specific install script. If so return
95            ** the required information. Otherwise return
96            ** all the rows with class = 23.
97            */
98            if (@version IS NULL)
99            begin --{
100               select object_cinfo as Script, char_value as Version,
101                   comments as "Start_End_Date",
102                   case int_value
103                       when 0 then 'Incomplete'
104                       else 'Complete'
105                   end as "Status"
106               into #sysattributes
107               from master.dbo.sysattributes
108               where class = @class and object_type = @featurecode
109                   and attribute = @attr_code
110                   and (@script_file IS NULL
111                       or object_cinfo like @script_file)
112   
113               /*
114               ** If verbose option is set then print all the information 
115               ** including the date and time information.
116               */
117   
118               if (@verbose IS NULL)
119               begin
120                   exec sp_autoformat #sysattributes,
121                       "Script, Version, Status", null,
122                       "ORDER BY 1"
123               end
124               else if (@verbose = 'all')
125               begin
126                   exec sp_autoformat #sysattributes,
127                       "Script, Version, Status, 
128   					'Start/End Date' = Start_End_Date",
129                       null, "ORDER BY 1"
130               end
131               else
132               begin
133                   raiserror 19194, "verbose", "all"
134                   return (1)
135               end
136           end --}
137           else
138           begin --{
139               /* 
140               ** version is non-null means it is updating / insert mode
141               ** raise appropriate error messages for script file and
142               ** code value.
143               */
144               if (@script_file IS NULL)
145                   raiserror 19381, "script_file"
146               raiserror 19194, 'code', "'start', 'end'."
147               return (1)
148           end --}
149           return (0)
150       end --}
151       else if (@version IS NULL or @script_file IS NULL)
152       begin
153           /*
154           ** If code is not NULL then check if script_file or version is NULL.
155           ** If any of them are NULL then give an appropriate error message.
156           */
157           if (@version IS NULL)
158               raiserror 19381, "version"
159           if (@script_file IS NULL)
160               raiserror 19381, "script_file"
161           if (@code != 'start' and @code != 'end')
162               raiserror 19194, 'code', "'start', 'end'"
163           return (1)
164       end
165       else
166       begin
167           /*
168           ** If all the above conditions are satisfied and code is not NULL
169           ** then we need to insert / update the version string. To do this
170           ** check whether the use has 'sa_role' if GP is not enabled. Else,
171           ** user needs 'own database' permission on master. proc_role and 
172           ** proc_auditperm will also do auditing if required. Both will also 
173           ** print error message if required.
174           */
175           select @dbname = db_name()
176           execute @status = sp_aux_checkroleperm "sa_role",
177               "own database", @dbname, @gp_enabled output
178   
179           /* Send apropriate audit records. */
180           if (@gp_enabled = 0)
181           begin
182               if (proc_role("sa_role") = 0)
183                   return (1)
184   
185           end
186           else
187           begin
188               select @dummy = proc_auditperm("own database", @status, @dbname)
189           end
190   
191           if (@status != 0)
192               return 1
193       end
194   
195       if (@code = 'start')
196       begin --{
197           /*
198           ** code = 'start' means the begining of the installation (install
199           ** script). Before adding a new row to sysattributes check
200           ** for the previous rows and take appropriate action.
201           */
202   
203           begin tran update_version_string
204   
205           delete from master.dbo.sysattributes
206           where class = @class and object_type = @featurecode
207               and attribute = @attr_code
208               and object_cinfo = @script_file
209   
210           select @loc_error = @@error, @loc_rowcount = @@rowcount
211   
212           if (@loc_error != 0) --{
213           begin
214               raiserror 19380, "sysattributes"
215               rollback update_version_string
216               return (2)
217           end --}
218   
219           if (@loc_rowcount > 1) --{
220           begin
221               raiserror 19378, "sysattributes", @@rowcount, "1"
222               rollback update_version_string
223               return (2)
224           end --}
225   
226           insert into master.dbo.sysattributes(class, object_type,
227               attribute, object_cinfo, char_value, int_value, comments)
228           values (@class, @featurecode, @attr_code, @script_file,
229               @version, 0, "[Started=" + convert(varchar, getdate()) + "]")
230   
231           if (@@error != 0)
232           begin
233               raiserror 19380, "sysattributes"
234               rollback update_version_string
235               return (2)
236           end
237   
238           commit tran update_version_string
239           return (0)
240       end --}
241       else if (@code = 'end')
242       begin --{
243           /*
244           ** @code = 'end' represents the successfull installation
245           ** of the script. Change int_value to reflect this
246           ** Before updating check whether the script is 
247           ** is registered for the installation. If so update
248           ** row for the script file with int_value = 0.
249           ** Otherwise raise an error.
250           */
251   
252           begin tran complete_version_string
253   
254           /*
255           ** The script is registered at the begining of the
256           ** Installtaion.
257           */
258   
259           select @date = convert(varchar, getdate())
260   
261           select @start_date = comments
262           from master.dbo.sysattributes
263           where class = @class and attribute = @attr_code
264               and object_type = @featurecode
265               and object_cinfo = @script_file
266   
267   
268           if (@start_date IS NOT NULL) --{
269           begin
270               select @end_date = @start_date
271                   + '-[Completed=' + @date + ']'
272   
273               update master.dbo.sysattributes
274               set int_value = 1, comments = @end_date
275               where class = @class and attribute = @attr_code
276                   and object_type = @featurecode
277                   and object_cinfo = @script_file
278   
279               select @loc_error = @@error, @loc_rowcount = @@rowcount
280   
281               if (@loc_error != 0) --{	
282               begin
283                   raiserror 19380, "sysattributes"
284                   rollback complete_version_string
285                   return (2)
286               end --}
287   
288               if (@loc_rowcount > 1) --{	
289               begin
290                   raiserror 19379, "sysattributes", @@rowcount, "1"
291                   rollback complete_version_string
292                   return (2)
293               end --}
294           end --}
295           else
296           begin --{	
297   
298               select @end_date = "[Completed=" + @date + "]"
299   
300               insert into master.dbo.sysattributes
301               (class, object_type, attribute, object_cinfo, char_value,
302                   int_value, comments)
303               values (@class, @featurecode, @attr_code, @script_file,
304                   @version, 1, @end_date)
305   
306               if (@@error != 0) --{
307               begin
308                   raiserror 19380, "sysattributes"
309                   rollback complete_version_string
310                   return (2)
311               end --}
312   
313           end --}
314   
315           commit tran complete_version_string
316           return (0)
317       end --}
318       else
319       begin
320           raiserror 19194, 'code', "'start', 'end'"
321           return (1)
322       end
323   


exec sp_procxmode 'sp_version', 'AnyMode'
go

Grant Execute on sp_version to public
go
DEFECTS
 MCTR 4 Conditional Begin Tran or Commit Tran 203
 MCTR 4 Conditional Begin Tran or Commit Tran 238
 MCTR 4 Conditional Begin Tran or Commit Tran 252
 MCTR 4 Conditional Begin Tran or Commit Tran 315
 MINU 4 Unique Index with nullable columns master..sysattributes master..sysattributes
 MTYP 4 Assignment type mismatch attribute: smallint = int 228
 MTYP 4 Assignment type mismatch class: smallint = int 228
 MTYP 4 Assignment type mismatch @start_date: varchar(150) = varchar(255) 261
 MTYP 4 Assignment type mismatch attribute: smallint = int 303
 MTYP 4 Assignment type mismatch class: smallint = int 303
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: parameter # 1 120
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: parameter # 1 126
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 108
 QTYP 4 Comparison type mismatch smallint = int 108
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 109
 QTYP 4 Comparison type mismatch smallint = int 109
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 206
 QTYP 4 Comparison type mismatch smallint = int 206
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 207
 QTYP 4 Comparison type mismatch smallint = int 207
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 263
 QTYP 4 Comparison type mismatch smallint = int 263
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 275
 QTYP 4 Comparison type mismatch smallint = int 275
 MGTP 3 Grant to public master..sysattributes  
 MGTP 3 Grant to public sybsystemprocs..sp_version  
 MNER 3 No Error Check should check @@error after select into 100
 MNER 3 No Error Check should check return value of exec 120
 MNER 3 No Error Check should check return value of exec 126
 MUCO 3 Useless Code Useless Brackets 90
 MUCO 3 Useless Code Useless Brackets 98
 MUCO 3 Useless Code Useless Brackets 118
 MUCO 3 Useless Code Useless Brackets 124
 MUCO 3 Useless Code Useless Brackets 134
 MUCO 3 Useless Code Useless Brackets 144
 MUCO 3 Useless Code Useless Brackets 147
 MUCO 3 Useless Code Useless Brackets 149
 MUCO 3 Useless Code Useless Brackets 151
 MUCO 3 Useless Code Useless Brackets 157
 MUCO 3 Useless Code Useless Brackets 159
 MUCO 3 Useless Code Useless Brackets 161
 MUCO 3 Useless Code Useless Brackets 163
 MUCO 3 Useless Code Useless Brackets 180
 MUCO 3 Useless Code Useless Brackets 182
 MUCO 3 Useless Code Useless Brackets 183
 MUCO 3 Useless Code Useless Brackets 191
 MUCO 3 Useless Code Useless Brackets 195
 MUCO 3 Useless Code Useless Brackets 212
 MUCO 3 Useless Code Useless Brackets 216
 MUCO 3 Useless Code Useless Brackets 219
 MUCO 3 Useless Code Useless Brackets 223
 MUCO 3 Useless Code Useless Brackets 231
 MUCO 3 Useless Code Useless Brackets 235
 MUCO 3 Useless Code Useless Brackets 239
 MUCO 3 Useless Code Useless Brackets 241
 MUCO 3 Useless Code Useless Brackets 268
 MUCO 3 Useless Code Useless Brackets 281
 MUCO 3 Useless Code Useless Brackets 285
 MUCO 3 Useless Code Useless Brackets 288
 MUCO 3 Useless Code Useless Brackets 292
 MUCO 3 Useless Code Useless Brackets 306
 MUCO 3 Useless Code Useless Brackets 310
 MUCO 3 Useless Code Useless Brackets 316
 MUCO 3 Useless Code Useless Brackets 321
 QAFM 3 Var Assignment from potentially many rows 261
 QCTC 3 Conditional Table Creation 100
 QIWC 3 Insert with not all columns specified missing 8 columns out of 15 226
 QIWC 3 Insert with not all columns specified missing 8 columns out of 15 301
 QPNC 3 No column in condition 110
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {object_type, object_cinfo, attribute, class}
108
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {object_type, object_cinfo, attribute, class}
206
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {object_type, object_cinfo, attribute, class}
263
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {object_type, object_cinfo, attribute, class}
275
 VNRD 3 Variable is not read @check_exist 80
 VNRD 3 Variable is not read @dummy 188
 MTR1 2 Metrics: Comments Ratio Comments: 41% 58
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 17 = 31dec - 16exi + 2 58
 MTR3 2 Metrics: Query Complexity Complexity: 125 58

DATA PROPAGATION detailed
ColumnWritten To
@script_filesysattributes.object_cinfo   sp_optgoal_rset_002.name sp_passwordpolicy_rset_001.value sp_rjs_retrieve_rset_001.js_server
@versionsysattributes.char_value   sysattributes.int_value   °.char_value   sp_dropdevice_rset_001.device sp_dropdevice_rset_001.device sp_forceonline_object_rset_001.status sp_forceonline_object_rset_002.status sp_forceonline_page_rset_001.status sp_forceonline_page_rset_002.status sp_listsuspect_object_rset_001.Access
sp_listsuspect_page_rset_001.Access sp_makesuspect_obj_rset_001.Indid °.LogType °.PageType °.ErrType °.Delay °.TotalNum sp_makesuspect_obj_rset_002.Indid °.LogType °.PageType
°.ErrType °.Delay °.TotalNum sp_makesuspect_obj_rset_003.Indid °.LogType °.PageType °.ErrType °.Delay °.TotalNum sp_passwordpolicy_rset_001.message
sp_passwordpolicy_rset_002.value sp_passwordpolicy_rset_003.value sp_passwordpolicy_rset_004.Policy_option sp_rjs_retrieve_rset_001.host_name sp_setsuspect_granularity_rset_001.Online mode sp_ssladmin_rset_001.certificate_path sp_ssladmin_rset_002.Cipher Suite Name °.Preference

DEPENDENCIES
PROCS AND TABLES USED
writes table tempdb..#sysattributes (1) 
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..syscurconfigs (1)  
   reads table master..sysconfigures (1)  
read_writes table master..sysattributes (1)  
calls proc sybsystemprocs..sp_autoformat  
   reads table master..syscolumns (1)  
   reads table tempdb..syscolumns (1)  
   writes table sybsystemprocs..sp_autoformat_rset_002 
   reads table master..systypes (1)  
   writes table sybsystemprocs..sp_autoformat_rset_003 
   calls proc sybsystemprocs..sp_namecrack  
   reads table tempdb..systypes (1)  
   writes table sybsystemprocs..sp_autoformat_rset_001 
   read_writes table tempdb..#colinfo_af (1) 
   writes table sybsystemprocs..sp_autoformat_rset_005 
   calls proc sybsystemprocs..sp_autoformat  
   writes table sybsystemprocs..sp_autoformat_rset_004