Database	Proc	Application	Created	Links
sybsystemprocs	sp_dboption_flmode		31 Aug 14	Defects Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/src/%M% %I% %G%" */
3     
4     /*
5     ** Messages for "sp_dboption_flmode"    
6     **
7     ** 17421, "No such database -- run sp_helpdb to list databases."
8     ** 17422, "The 'master' database's options can not be changed."
9     ** 17423, "Usage: sp_dboption [dbname, optname, {true | false}]"
10    ** 17424, "Database option doesn't exist or can't be set by user."
11    ** 17425, "Run sp_dboption with no parameters to see options."
12    ** 17953, "The full logging mode cannot be defined for '%1!'."
13    ** 17431, "true"
14    ** 17432, "false"
15    */
16    
17    /* 
18    ** IMPORTANT: Please read the following instructions before
19    **   making changes to this stored procedure.
20    **
21    **	To make this stored procedure compatible with High Availability (HA),
22    **	changes to certain system tables must be propagated to the companion 
23    **	server under some conditions.
24    **	The tables include (but are not limited to):
25    **		syslogins, sysservers, sysattributes, systimeranges,
26    **		sysresourcelimits, sysalternates, sysdatabases,
27    **		syslanguages, sysremotelogins, sysloginroles,
28    **		sysalternates (master DB only), systypes (master DB only),
29    **		sysusers (master DB only), sysprotects (master DB only)
30    **	please refer to the HA documentation for details.
31    **
32    **	Here is what you need to do: 
33    **	For each insert/update/delete statement, add three sections to
34    **	-- start HA transaction prior to the statement
35    **	-- add the statement
36    **	-- add HA synchronization code to propagate the change to the companion
37    **
38    **	For example, if you are adding 
39    **		insert master.dbo.syslogins ......
40    **	the code should look like:
41    **	1. Before that SQL statement:
42    **		
43    **	2. Now, the SQL statement:
44    **		insert master.dbo.syslogins ......
45    **	3. Add a HA synchronization section right after the SQL statement:
46    **		
47    **
48    **	You may need to do similar change for each built-in function you
49    **	want to add.
50    **
51    **	Finally, add a separate part at a place where it can not
52    **	be reached by the normal execution path:
53    **	clean_all:
54    **		
55    **		return (1)
56    */
57    
58    create procedure sp_dboption_flmode
59        @dbname varchar(30) = null,
60        @optname varchar(30) = null,
61        @optvalue varchar(10) = null
62    as
63    
64        declare @msg varchar(1024),
65            @fl_class smallint,
66            @action smallint,
67            @HA_CERTifIED tinyint, /* Is the SP HA certified ? */
68            @retstat int,
69            @type char(2),
70            @optval int,
71            @true varchar(10),
72            @false varchar(10),
73            @dbid int,
74            @dbuid int,
75            @bit int,
76            @bitmap int,
77            @newbitmap int,
78            @haproc sysname
79    
80        /*
81        **  Verify the database name and get the @dbid and @dbuid
82        */
83        select @dbid = dbid, @dbuid = suid
84        from master.dbo.sysdatabases
85        where name = @dbname
86    
87        /* 
88        ** If @dbname not found raise an error. 
89        */
90        if @dbid is null
91        begin
92            raiserror 17421
93            return (1)
94        end
95    
96        /*
97        **  Only the Database Owner (DBO) or
98        **  Accounts with SA role can execute it.
99        **  Call proc_role() with the required SA role.
100       */
101       if ((suser_id() != @dbuid) and (proc_role("sa_role") < 1))
102           return (1)
103   
104       /*
105       **  You can not change any of the options in master.  If the user tries to
106       **  do so tell them they can't.
107       */
108       if @dbid = 1
109       begin
110           raiserror 17422
111           return (1)
112       end
113   
114       /*
115       ** The full logging options are stored in master..sysattributes as:
116       **
117       **	- class:	38
118       **	- type:		'D'
119       **
120       **      attribute char_value	    object      object_info1
121       **      ----------------------------------------------------
122       **	0	  NULL		    	
123       **      0	  all		    1		0x0000000f
124       **      1	  select into       1		0x00000001
125       **      3	  alter table       1		0x00000004
126       **      4	  reorg rebuild     1		0x00000008
127       **
128       ** The database master stores only the descriptions, so, the
129       ** attribute 0 that in other databases stores the database bitmap,
130       ** in the case of master it's used just to store the description
131       ** 'all' for the bitmap 0xd. This is ok because the full 
132       ** logging options cannot be changed in master.
133       ** 
134       ** If there is an attribute configured, we will update it, otherwise
135       ** we will insert a new row.
136       **
137       */
138   
139       exec sp_getmessage 17431, @true out
140       exec sp_getmessage 17432, @false out
141   
142       /*
143       ** If the option name is null, the current values will be displayed
144       */
145       select @fl_class = 38, @type = 'D'
146   
147       if (@optname is null)
148       begin
149           select t1.char_value as mode,
150               case when t1.object_info1 & t2.object_info1 = 0
151                   then @false else @true
152               end as enable
153           into #tmp
154           from master.dbo.sysattributes t1, master.dbo.sysattributes t2
155           where
156               t1.class = @fl_class
157               and t2.class = @fl_class
158               and t1.object = 1
159               and t2.object = @dbid
160               and t1.attribute != 0
161               and t2.attribute = 0
162   
163           exec sp_autoformat
164               @fulltabname = '#tmp',
165               @selectlist = '''Logging option'' = mode, 
166   				''Enabled'' = enable'
167           return 0
168       end
169   
170       select @optvalue = lower(@optvalue)
171       if @optvalue in ("true", @true)
172           select @optval = 1
173       else if @optvalue in ("false", @false)
174           select @optval = 0
175       else
176       begin
177           raiserror 17423
178           return (1)
179       end
180   
181       select @HA_CERTifIED = 0
182       select @retstat = 0
183   
184   
185   
186       /* check to see if we are using HA specific SP for a HA enabled server */
187       select @haproc = "sp_ha_check_certified"
188       exec @retstat = @haproc 'sp_dboption_flmode', @HA_CERTifIED
189       if (@retstat != 0)
190           return (1)
191   
192       /*
193       ** Check whether a logging mode exists. Option name must exist for the
194       ** database master (object = 1).
195       **
196       */
197       select @bit = object_info1
198       from master.dbo.sysattributes
199       where class = @fl_class
200           and object = 1
201           and char_value = @optname
202   
203       if @bit is null
204       begin
205           raiserror 17953, @optname
206           return (1)
207       end
208   
209       /*
210       ** Do we have an entry in sysattributes for this database ? attribute = 0
211       ** and object = @dbid
212       */
213       select @bitmap = object_info1
214       from master.dbo.sysattributes
215       where class = @fl_class
216           and object = @dbid
217           and attribute = 0
218   
219       if @bitmap is null
220           select @bitmap = 0, @action = 1 /* insert */
221       else
222           select @action = 2 /* update */
223   
224       /* Set or reset the bit. */
225       if (@optval = 0)
226           select @newbitmap = @bitmap & ~ @bit
227       else
228           select @newbitmap = @bitmap | @bit
229   
230       /*
231       ** First validate the row. 
232       */
233       if attrib_valid(@fl_class, 0, @type, @dbid, @newbitmap, null, null,
234               null, null, null, null, null, null, @action) = 0
235           return 1
236   
237   
238   
239       /*
240       ** Now update the row
241       */
242       if (@action = 1)
243           insert master.dbo.sysattributes
244           (class, attribute, object_type, object, object_info1)
245           values (@fl_class, 0, @type, @dbid, @newbitmap)
246       else
247           update master.dbo.sysattributes
248           set object_info1 = @newbitmap
249           where class = @fl_class
250               and object = @dbid
251               and attribute = 0
252   
253       if (@@error != 0)
254           goto clean_fl_all
255   
256   
257   
258       /*
259       ** Sync the in-memory RDES with the new values
260       ** in sysattributes.
261       */
262       if attrib_notify(@fl_class, 0, @type, @dbid, @newbitmap, null, null,
263               null, null, null, null, null, null, @action) = 0
264           return (1)
265       else
266           return (@retstat)
267   
268   clean_fl_all:
269   
270   
271   
272       return (1)
273   
274



exec sp_procxmode 'sp_dboption_flmode', 'AnyMode'
go

Grant Execute on sp_dboption_flmode to public
go

DEFECTS
QCAR 6 Cartesian product between tables master..sysattributes t1 and [master..sysattributes t2]	154
MINU 4 Unique Index with nullable columns master..sysattributes	master..sysattributes
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	160
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	161
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	217
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	251
MGTP 3 Grant to public master..sysattributes
MGTP 3 Grant to public master..sysdatabases
MGTP 3 Grant to public sybsystemprocs..sp_dboption_flmode
MNER 3 No Error Check should check return value of exec	139
MNER 3 No Error Check should check return value of exec	140
MNER 3 No Error Check should check @@error after select into	149
MNER 3 No Error Check should check return value of exec	163
MNER 3 No Error Check should check @@error after insert	243
MNER 3 No Error Check should check @@error after update	247
MUCO 3 Useless Code Useless Brackets	93
MUCO 3 Useless Code Useless Brackets	101
MUCO 3 Useless Code Useless Brackets	102
MUCO 3 Useless Code Useless Brackets	111
MUCO 3 Useless Code Useless Brackets	147
MUCO 3 Useless Code Useless Brackets	178
MUCO 3 Useless Code Useless Brackets	189
MUCO 3 Useless Code Useless Brackets	190
MUCO 3 Useless Code Useless Brackets	206
MUCO 3 Useless Code Useless Brackets	225
MUCO 3 Useless Code Useless Brackets	242
MUCO 3 Useless Code Useless Brackets	253
MUCO 3 Useless Code Useless Brackets	264
MUCO 3 Useless Code Useless Brackets	266
MUCO 3 Useless Code Useless Brackets	272
MUOT 3 Updates outside transaction	247
MUPK 3 Update column which is part of a PK or unique index object_info1	248
QAFM 3 Var Assignment from potentially many rows	197
QAFM 3 Var Assignment from potentially many rows	213
QCTC 3 Conditional Table Creation	149
QIWC 3 Insert with not all columns specified missing 10 columns out of 15	244
QNAJ 3 Not using ANSI Inner Join	154
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, class, attribute}	156
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, class, attribute}	157
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, class}	199
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, class, attribute}	215
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, class, attribute}	249
VUNU 3 Variable is not used @msg	64
MDYE 2 Dynamic Exec Marker exec @retstat	188
MTR1 2 Metrics: Comments Ratio Comments: 55%	58
MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 20 = 24dec - 6exi + 2	58
MTR3 2 Metrics: Query Complexity Complexity: 94	58