DatabaseProcApplicationCreatedLinks
sybsystemprocssp_memlog  31 Aug 14Defects Dependencies

1     
2     /*
3     ** SP_MEMLOG
4     **
5     ** This file contains the definition of the sp_memlog system
6     ** stored procedure.  This stored procedure is used to change settings
7     ** of Memory Log.
8     ** The sa_role is required to make any changes to the
9     ** system configuration.
10    **
11    ** Security: The sa_role role is required in order to change memlog settings.
12    **
13    ** History:
14    ** May 2006	frankl		Initial coding, based on sp_shmdumpconfig
15    */
16    
17    create procedure sp_memlog
18        /* -------------- Stored Procedure Parameters ----------------------- */
19        @action varchar(20) = "help", /* action requested by user   */
20        @dumps_per_file int = 10 /* dump count value           */
21    as
22    
23        /* ----------------- Declare Local Variables ------------------------ */
24        declare @object_info1 int, /* object_info1 column value  */
25            @attrib int, /* attribute column value     */
26            @not_status int, /* notification return status */
27            @int_value int
28    
29        /*
30        ** The following variables are treated as constants within this
31        ** stored procedure. They are set below.
32        */
33        declare @MEMLOG_CLASS int, /* sysattributes class        */
34            @OBJTYPE char(2), /* sysattributes object typ   */
35            @ATTR_ADD int, /* add notification value     */
36            @ATTR_CHANGE int, /* change notification val    */
37            @ATTR_DROP int, /* drop notification value    */
38            @ATTR_FETCH int /* fetch notification value   */
39    
40        /* ----------------- Setup and Validation ------------------------ */
41        set nocount on
42    
43        /*
44        ** Class ID and type defined in utils/attrib.lst
45        */
46        select @MEMLOG_CLASS = 30
47        select @OBJTYPE = "ML"
48    
49        /*
50        ** The following must correspond to values in sysattr.h
51        */
52        select @ATTR_ADD = 1
53        select @ATTR_CHANGE = 2
54        select @ATTR_DROP = 3
55        select @ATTR_FETCH = 4
56    
57        /*
58        ** Determine the attribute number and int_value for the attribute indicated
59        ** by the @action.
60        **
61        ** NOTE: The integer values used for attrib must correspond to values
62        ** defined in utils/attrib.lst.  Any changes made here must also be made
63        ** in attrib.lst, and vice versa.
64        **	0: suspend
65        **	1: sequence numbers
66        **	2: dump when full
67        **	3: dump destination
68        */
69        create table #actions(attrib int, int_val int, action varchar(20))
70    
71        insert #actions select 0, 0, 'enable logging'
72        insert #actions select 0, 1, 'disable logging'
73        insert #actions select 1, 0, 'disable sequence num'
74        insert #actions select 1, 1, 'enable sequence num'
75        insert #actions select 2, 0, 'no dump when full'
76        insert #actions select 2, 1, 'dump when full'
77    
78        /*
79        ** The following constants define record types for the melog dump destination
80        ** defined in cinclude/memlog.h.
81        */
82        insert #actions select 3, 0, 'dump to errorlog'
83        insert #actions select 3, 1, 'dump to file'
84        insert #actions select 3, 2, 'dump to shm'
85        insert #actions select 3, 3, 'dump to table'
86        insert #actions select 3, 4, 'disable dump'
87    
88        /*
89        ** End Common Definition Section
90        */
91    
92    
93        if @action = 'help'
94        begin
95    usage:
96            print ""
97            print "Usage: sp_memlog 'help'"
98            print "       sp_memlog 'list'"
99            print "       sp_memlog 'enable logging' | 'disable logging'"
100           print "       sp_memlog 'enable sequence num' | 'disable sequence num'"
101           print "       sp_memlog 'dump to errorlog' | 'dump to file'"
102           print "               | 'dump to shm' | 'dump to table' | 'disable dump'"
103           print "       sp_memlog '[no ]dump when full' [, dumps_per_file]"
104   
105           return 0
106       end
107   
108       if @action = 'list'
109       begin
110           goto list
111       end
112   
113       /*
114       ** Change settings section
115       */
116       if (proc_role("sa_role") < 1)
117       begin
118           return 2
119       end
120   
121       /*
122       ** Translate @action to attrib and int_value.
123       */
124       select @attrib = attrib, @int_value = int_val
125       from #actions
126       where action = @action
127   
128       if @@rowcount = 0
129       begin
130           /*
131           ** 18516, "Invalid action %1!"
132           */
133           raiserror 18516, @action
134           goto usage
135       end
136   
137       /*
138       ** Special case for 'dump when full': object_info1
139       ** becomes number of dumps per file.
140       */
141       select @object_info1 = NULL
142   
143       if @attrib = 2
144       begin
145           select @object_info1 = @dumps_per_file
146       end
147   
148       begin tran ChangeMemlog
149       /*
150       ** Delete existing entry, if there is one
151       */
152       delete master.dbo.sysattributes
153       where class = @MEMLOG_CLASS
154           and attribute = @attrib
155           and object_type = @OBJTYPE
156   
157       /*
158       ** Insert new values
159       */
160       insert master.dbo.sysattributes
161       (class, attribute, object_type,
162           int_value, object_info1)
163       values (@MEMLOG_CLASS, @attrib, @OBJTYPE,
164           @int_value, @object_info1)
165   
166       if (@@error = 0)
167       begin
168           select @not_status
169               = attrib_notify(@MEMLOG_CLASS, /*cl*/
170                   @attrib, /*attrib */
171                   @OBJTYPE, /*type*/
172                   NULL, /*object*/
173                   @object_info1, /*info1*/
174                   0, /*info2*/
175                   NULL, /*info3*/
176                   NULL, /*cinfo*/
177                   @int_value, /*intval*/
178                   NULL, /*charval*/
179                   NULL, /*textval*/
180                   NULL, /*imageval*/
181                   NULL, /*comment*/
182                   @ATTR_CHANGE)
183   
184           if (@not_status = 0)
185           begin
186               /*
187               ** 18505, "Notification failed. Condition not set."
188               */
189               raiserror 18505
190               goto error_exit
191           end
192       end
193       else
194       begin
195           goto error_exit
196       end
197   
198       commit ChangeMemlog
199   
200   list:
201       /*
202       ** Display current setting based on what is in sysattributes
203       */
204       print "Current settings"
205       print ""
206       select act.action,
207           dumps_per_file = att.object_info1
208       from master.dbo.sysattributes att,
209           #actions act
210       where att.class = @MEMLOG_CLASS
211           and object_type = @OBJTYPE
212           and act.attrib = att.attribute
213           and act.int_val = att.int_value
214   
215       /*
216       ** Indicate success
217       */
218       return 0
219   
220   error_exit:
221       /*
222       ** An error occured while inserting rows into the sysattributes table.
223       */
224       if (@@trancount > 0)
225       begin
226           /*
227           ** Rollback the current transaction
228           */
229           rollback tran ChangeMemlog
230       end
231   
232       return 18
233   
234   /*
235   ** End of sp_memlog
236   */
237   


exec sp_procxmode 'sp_memlog', 'AnyMode'
go

Grant Execute on sp_memlog to public
go
RESULT SETS
sp_memlog_rset_001

DEFECTS
 MEST 4 Empty String will be replaced by Single Space 96
 MEST 4 Empty String will be replaced by Single Space 205
 MINU 4 Unique Index with nullable columns master..sysattributes master..sysattributes
 MTYP 4 Assignment type mismatch attribute: smallint = int 163
 MTYP 4 Assignment type mismatch class: smallint = int 163
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 153
 QTYP 4 Comparison type mismatch smallint = int 153
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 154
 QTYP 4 Comparison type mismatch smallint = int 154
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 210
 QTYP 4 Comparison type mismatch smallint = int 210
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint 212
 MGTP 3 Grant to public master..sysattributes  
 MGTP 3 Grant to public sybsystemprocs..sp_memlog  
 MNER 3 No Error Check should check @@error after insert 71
 MNER 3 No Error Check should check @@error after insert 72
 MNER 3 No Error Check should check @@error after insert 73
 MNER 3 No Error Check should check @@error after insert 74
 MNER 3 No Error Check should check @@error after insert 75
 MNER 3 No Error Check should check @@error after insert 76
 MNER 3 No Error Check should check @@error after insert 82
 MNER 3 No Error Check should check @@error after insert 83
 MNER 3 No Error Check should check @@error after insert 84
 MNER 3 No Error Check should check @@error after insert 85
 MNER 3 No Error Check should check @@error after insert 86
 MNER 3 No Error Check should check @@error after delete 152
 MUCO 3 Useless Code Useless Brackets 116
 MUCO 3 Useless Code Useless Brackets 166
 MUCO 3 Useless Code Useless Brackets 184
 MUCO 3 Useless Code Useless Brackets 224
 MUIN 3 Column created using implicit nullability 69
 QAFM 3 Var Assignment from potentially many rows 124
 QIWC 3 Insert with not all columns specified missing 10 columns out of 15 161
 QJWT 3 Join or Sarg Without Index on temp table 212
 QNAJ 3 Not using ANSI Inner Join 208
 QNUA 3 Should use Alias: Column object_type should use alias att 211
 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: {class, object_type, attribute}
153
 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: {class, object_type}
210
 VNRD 3 Variable is not read @ATTR_ADD 52
 VNRD 3 Variable is not read @ATTR_DROP 54
 VNRD 3 Variable is not read @ATTR_FETCH 55
 MRST 2 Result Set Marker 206
 MTR1 2 Metrics: Comments Ratio Comments: 41% 17
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 7 = 8dec - 3exi + 2 17
 MTR3 2 Metrics: Query Complexity Complexity: 96 17

DEPENDENCIES
PROCS AND TABLES USED
read_writes table master..sysattributes (1)  
read_writes table tempdb..#actions (1)