DatabaseProcApplicationCreatedLinks
sybsystemprocssp_setrepdbmode  14 déc. 14Defects Propagation Dependencies

1     
2     /*
3     ** Message for "sp_setrepdbmode"
4     ** 17260, "Can't run %1! from within a transaction."
5     ** 18408, "The stored procedure must be executed with the name of
6     **         the current database."
7     ** 18409, "The built-in function '%1!' failed. Please see any 
8     **         other messages printed along with this message."
9     ** 18410, "The replication status of '%1!' is corrupt. Please contact 
10    **         Sybase Technical Support."
11    ** 18411, "The replication status for database '%1!' is '%2!'."
12    ** 18412, "The replication status for database '%1!' is already set to 
13    **         '%2!'. The replication status is not changed."
14    ** 18413, "Due to a system failure, the replication status for '%1!' 
15    **         has not been changed."
16    ** 18414, "The replication status for database '%1!' has been set to '%2!'."
17    ** 18418, "Only the System Administrator (SA), the Database Owner (dbo) 
18    **         or a user with REPLICATION authorization may execute this 
19    **         stored procedure."
20    ** 19888, "The attempt to set the replication mode failed. Please,
21    ** 	   set the database replication level to 'L1' or 'ALL' using
22    ** 	   the stored procedure sp_reptostandby prior to setting 
23    ** 	   the replication mode."
24    */
25    create or replace procedure sp_setrepdbmode
26        @dbname varchar(255) = NULL,
27        @setflag varchar(20) = NULL,
28        @setmode varchar(20) = NULL
29    as
30        declare @msg varchar(1024)
31        declare @off varchar(20)
32        declare @on varchar(20)
33        declare @procval int
34        declare @dbuid int
35        declare @curdb varchar(255)
36        declare @dbid int
37        declare @objid int
38        declare @rep_constant smallint
39        declare @tmpstr varchar(200)
40        declare @curstat int
41        declare @newstat int
42        declare @sptlang int
43        declare @setrep_status int
44        declare @lt_sqlrep_upd int
45        declare @lt_sqlrep_del int
46        declare @lt_sqlrep_inssel int
47        declare @lt_sqlrep_selinto int
48        declare @sqlrepmask int
49        declare @lt_rep_all int
50        declare @lt_rep_l1 int
51        declare @lt_rep_get_failed int
52    
53        declare @update char(1) /* 'u' string used for sql replication */
54        declare @delete char(1) /* 'd' string used for sql replication */
55        declare @inserts char(1) /* 'i' string used for sql replication */
56        declare @selinto char(1) /* 's' string used for sql replication */
57        declare @sqlrep varchar(4) /* 'udis' string used for sql replication */
58        declare @sqlrep_syntax int /* Are we using a valid substring of 'udis'? */
59    
60        /* SQL replication threshold */
61        declare @threshold varchar(20) /* Used to check stored proc syntax */
62        declare @current_threshold int /* Current database threshold */
63        declare @threshold_value int /* Integer value from @setflag */
64        declare @threshold_syntax smallint /* Used to check stored proc syntax */
65        declare @threshold_max int /* Max value for sql threshold */
66        declare @outvalue varchar(10) /* Used to stored tmp values */
67        declare @dummy int
68        declare @nullarg char(1)
69        declare @gp_enabled int
70        declare @status1 int
71        declare @status2 int
72        declare @status3 int
73    
74        select @status1 = 1
75        select @status2 = 1
76        select @status3 = 1
77    
78        /*
79        ** sp_setrepdbmode cannot be executed inside a transaction
80        */
81    
82        if @@trancount > 0
83        begin
84            /*
85            ** 17260, "Can't run %1! from within a transaction."
86            */
87            raiserror 17260, "sp_reptostandby"
88            return (1)
89        end
90    
91        set chained off
92        set transaction isolation level 1
93    
94        /*
95        ** Set up the constants 
96        */
97        select @rep_constant = - 32768,
98            @setrep_status = 0,
99            @lt_rep_get_failed = - 2, /* LT_GET_REP_FAILED */
100           @lt_sqlrep_upd = 32, /* LT_SQLREP_UPD */
101           @lt_sqlrep_del = 64, /* LT_SQLREP_DEL */
102           @lt_sqlrep_inssel = 128, /* LT_SQLREP_INSSEL */
103           @lt_sqlrep_selinto = 256, /* LT_SQLREP_SELINTO */
104           @lt_rep_all = 2048, /* LT_REP_ALL */
105           @lt_rep_l1 = 4096, /* LT_REP_L1 */
106           @threshold_max = 10000 /* LT_SQLREP_MAX_THRESHOLD */
107   
108       select @sqlrep = name
109       from master.dbo.spt_values
110       where type = 'SQ'
111           and number = 1
112   
113       /*
114       ** get localized parameters for "on","off" and "threshold"
115       */
116       exec sp_getmessage 19895, @off out
117       exec sp_getmessage 19897, @on out
118       exec sp_getmessage 19931, @threshold out
119   
120       if (@sqlrep is NULL or @on is NULL or @off is NULL or @threshold is NULL)
121       begin
122           /*
123           ** 18413, "Due to a system failure, the replication status
124           ** for '%1!' has not been changed."
125           */
126           raiserror 18413, @dbname
127           return (1)
128       end
129   
130       select @update = substring(@sqlrep, 1, 1)
131       select @delete = substring(@sqlrep, 2, 1)
132       select @inserts = substring(@sqlrep, 3, 1)
133       select @selinto = substring(@sqlrep, 4, 1)
134   
135       /*
136       ** Set 'sptlang' for proper printing of object information.  Used mainly
137       ** for the 'select' statement which is executed when we are invoked with
138       ** no parameters.  Copied from similar code in 'sp_help'
139       */
140       select @sptlang = @@langid
141       if @@langid != 0
142       begin
143           if not exists (
144                   select * from master.dbo.sysmessages where error
145                       between 17100 and 17109
146                       and langid = @@langid)
147               select @sptlang = 0
148       end
149   
150       select @setflag = lower(@setflag)
151       select @setmode = lower(@setmode)
152   
153       /* check the syntax */
154       if (@dbname is NULL)
155       begin
156           /*
157           ** 19887, "Usage: sp_setrepdbmode dbname [, 'UDIS' [, 'on' | 'off']
158           **				| 'threshold' [,'value']] 
159           */
160           raiserror 19887
161           return (1)
162       end
163   
164   
165       /* Determine the database */
166       select @curdb = db_name()
167   
168       /*
169       ** make sure the procedure is executed with the name of the current
170       ** database
171       */
172       if (@dbname != @curdb)
173       begin
174           /*
175           ** 18408, "The stored procedure must be executed with the name of the
176           **         current database."
177           */
178           raiserror 18408
179           return (1)
180       end
181   
182       /*
183       ** Verify the database name and get the @dbuid
184       */
185       select @dbuid = suid
186       from master.dbo.sysdatabases
187       where name = @dbname
188   
189       /* Do security check */
190   
191       /*
192       ** If granular permissions is not enabled, 
193       ** you must be SA, dbo or have REPLICATION role to execute this
194       ** sproc.
195       **  If granular permissions is enabled then users with 
196       ** 'manage replication' permission can execute it.
197       */
198       select @nullarg = NULL
199       execute @status1 = sp_aux_checkroleperm "dbo", "manage replication",
200           @dbname, @gp_enabled output
201   
202       if (@gp_enabled = 0)
203       begin
204           if (@status1 != 0)
205           begin
206               execute @status2 = sp_aux_checkroleperm "sa_role",
207                   @nullarg, @nullarg, @gp_enabled output
208   
209               if (@status2 != 0)
210                   execute @status3 = sp_aux_checkroleperm
211                       "replication_role", @nullarg, @nullarg,
212                       @gp_enabled output
213   
214               if (@status3 != 0)
215               begin
216                   /*
217                   ** 18418, "Only the System Administrator (SA), the 
218                   ** Database Owner (dbo) or a user with REPLICATION 
219                   ** authorization may execute this stored procedure."
220                   */
221                   raiserror 18418
222                   return (1)
223               end
224           end
225           if (@status2 = 0)
226               select @dummy = proc_role("sa_role")
227           if (@status3 = 0)
228               select @dummy = proc_role("replication_role")
229       end
230       else
231       begin
232           select @dummy = proc_auditperm("manage replication", @status1, @dbname)
233           if (@status1 != 0)
234               return 1
235       end
236   
237       /*
238       ** Get current dbid
239       */
240       select @dbid = db_id()
241   
242       select @sqlrepmask =
243           @lt_sqlrep_upd | @lt_sqlrep_del | @lt_sqlrep_inssel | @lt_sqlrep_selinto
244   
245       /*
246       ** Check that the arguments to this procedure are acceptable for
247       ** further processing. The arguments are @setflag and @setmode.
248       */
249       select @sqlrep_syntax = 0,
250           @threshold_syntax = 0
251   
252       /*
253       ** @sqlrep_syntax is set to 1 if @setflag is a valid substring of 
254       ** 'udis', and to 0 otherwise.
255       */
256       exec sp_sqlrep_check_syntax @setflag,
257           @sqlrep,
258           @sqlrep_syntax output
259   
260   
261       /*
262       ** We display sp_setrepdbmode status in the following cases
263       **
264       **	1.- 'sp_setrepdbmode '
265       **	2.- 'sp_setrepdbmode ', '
266       ** 	3.- 'sp_setrepdbmode ', 'threshold'
267       **
268       */
269       if ((@setflag is null) or
270               (@setmode is null and @sqlrep_syntax = 1) or
271               (@setmode is null and @setflag in ("threshold", @threshold)))
272       begin
273           /*
274           ** First, determine the current replication status of the database.
275           */
276           select @curstat = getdbrepstat()
277           if (@curstat = @lt_rep_get_failed)
278           begin
279               /*
280               ** "The built-in function getdbrepstat() failed. Please see any
281               **  other messages printed along with this message."
282               */
283               raiserror 18409, "getdbrepstat"
284               return (1)
285           end
286   
287           if ((@curstat & @sqlrepmask) != 0)
288           begin
289               select @tmpstr = " "
290               if ((@curstat & @lt_sqlrep_upd) = @lt_sqlrep_upd)
291                   select @tmpstr = @tmpstr + "u"
292               if ((@curstat & @lt_sqlrep_del) = @lt_sqlrep_del)
293                   select @tmpstr = @tmpstr + "d"
294               if ((@curstat & @lt_sqlrep_inssel) = @lt_sqlrep_inssel)
295                   select @tmpstr = @tmpstr + "i"
296               if ((@curstat & @lt_sqlrep_selinto) = @lt_sqlrep_selinto)
297                   select @tmpstr = @tmpstr + "s"
298           end
299           else
300               select @tmpstr = @off
301   
302           /* Get the current replication threshold */
303           select @current_threshold = getrepdbthreshold(@dbid)
304           if (@current_threshold < 0)
305           begin
306               /*
307               ** "The built-in function getrepthreshold() failed.
308               ** Please see any other messages printed along with
309               ** this message."
310               */
311               raiserror 18409, "getrepdbthreshold"
312               return (1)
313           end
314   
315           /*
316           ** 19893, "The replication mode for database '%1!' is '%2!'"
317           */
318           exec sp_getmessage 19893, @msg output
319           print @msg, @dbname, @tmpstr
320   
321           /*
322           ** 19932, "The replication threshold for '%1!' is '%2!'"
323           */
324           exec sp_getmessage 19932, @msg output
325           select @outvalue = convert(varchar(10), @current_threshold)
326           print @msg, @dbname, @outvalue
327           return (0)
328       end
329   
330       select @setflag = ltrim(rtrim(@setflag))
331   
332       if (@setflag in ("threshold", @threshold))
333       begin
334           select @threshold_syntax = 1
335       end
336       else
337       if (@sqlrep_syntax = 1 and @setmode not in ("on", "off", @on, @off, NULL))
338       begin
339           select @sqlrep_syntax = 0
340       end
341   
342       if (@sqlrep_syntax = 0 and @threshold_syntax = 0)
343       begin
344           /*
345           ** 19887, "Usage: sp_setrepdbmode dbname [, 'UDIS' [, 'on' | 'off']
346           **				| 'threshold' [,'value']] 
347           */
348           raiserror 19887
349           return (1)
350       end
351   
352       if (@sqlrep_syntax = 1)
353       begin
354           /*
355           ** First, determine the current replication status of the database.
356           */
357           select @curstat = getdbrepstat()
358           if (@curstat = @lt_rep_get_failed)
359           begin
360               /*
361               ** "The built-in function getdbrepstat() failed. Please see any
362               **  other messages printed along with this message."
363               */
364               raiserror 18409, "getdbrepstat"
365               return (1)
366           end
367   
368           /* Database replication level has to be set */
369           if ((@curstat & @lt_rep_all != @lt_rep_all) and
370                   (@curstat & @lt_rep_l1 != @lt_rep_l1))
371           begin
372               /*
373               ** 19888: The attempt to set the replication mode failed. Please,
374               ** set the database replication level to 'L1' or 'ALL'
375               ** using the stored procedure sp_reptostandby prior to setting 
376               ** the replication mode.
377               */
378               raiserror 19888
379               return (1)
380           end
381   
382           /* Interpret the input string: "udis" */
383           if (charindex("u", @setflag) > 0)
384           begin
385               select @setrep_status = @setrep_status | @lt_sqlrep_upd
386           end
387           if (charindex("d", @setflag) > 0)
388           begin
389               select @setrep_status = @setrep_status | @lt_sqlrep_del
390           end
391           if (charindex("i", @setflag) > 0)
392           begin
393               select @setrep_status = @setrep_status | @lt_sqlrep_inssel
394           end
395           if (charindex("s", @setflag) > 0)
396           begin
397               select @setrep_status = @setrep_status | @lt_sqlrep_selinto
398           end
399   
400           if (@setmode = @off)
401           begin
402               select @setrep_status = 0
403           end
404   
405           /* No Change; we're done. */
406           if ((@setrep_status & @sqlrepmask) = (@curstat & @sqlrepmask))
407           begin
408               /*
409               ** 18412, "The replication status for database '%1!' is already 
410               ** set to '%2!'. The replication status is not changed."
411               */
412               exec sp_getmessage 18412, @msg output
413               if (@setmode = @off)
414                   print @msg, @dbname, @setmode
415               else
416                   print @msg, @dbname, @setflag
417               return (0)
418           end
419   
420           /* Do set */
421           if (setdbrepstat(@setrep_status, 1) != 1)
422           begin
423               /*
424               ** 18413, "Due to a system failure, the replication status
425               ** for '%1!' has not been changed."
426               */
427               raiserror 18413, @dbname
428               return (1)
429           end
430   
431           /* Let user know what we did. */
432           if (@setmode = @off)
433           begin
434               /*
435               ** 19893, "The replication mode for database '%1!' is '%2!'."
436               */
437               exec sp_getmessage 19893, @msg output
438               print @msg, @dbname, @off
439           end
440           else
441           begin
442               /* Display status message */
443               /*
444               ** 19893, "The replication mode for database '%1!' is '%2!'."
445               */
446               exec sp_getmessage 19893, @msg output
447               print @msg, @dbname, @setflag
448           end
449           return (0)
450       end
451       else
452       /* We are changing the sql replication threshold */
453       if (@threshold_syntax = 1)
454       begin
455           if (@setmode is null)
456           begin
457               /* Get the current replication threshold */
458               select @current_threshold = getrepdbthreshold(@dbid)
459   
460               if (@current_threshold < 0)
461               begin
462                   /*
463                   ** "The built-in function getrepthreshold() failed.
464                   ** Please see any other messages printed along with
465                   ** this message."
466                   */
467                   raiserror 18409, "getrepdbthreshold"
468                   return (1)
469               end
470   
471               /*
472               ** 19932, "The replication threshold for '%1!' is '%2!'."
473               */
474               exec sp_getmessage 19932, @msg output
475               select @outvalue = convert(varchar(10), @current_threshold)
476               print @msg, @dbname, @outvalue
477               return (0)
478           end
479   
480           select @threshold_value = convert(int, @setmode)
481   
482           if ((@threshold_value < 0) or (@threshold_value > @threshold_max))
483           begin
484               /*
485               ** 19934, "The replication threshold provided for
486               **        '%1!' is '%2!'. Please choose a
487               **        threshold larger than 0 and smaller than '%3!'."
488               */
489               select @outvalue = convert(varchar(10), @threshold_max)
490               raiserror 19934, @dbname, @setmode, @outvalue
491               return (1)
492           end
493   
494           if (setrepdbthreshold(@dbid, @threshold_value) != 1)
495           begin
496               /*
497               ** 18409, "The built-in funtion '%1!' failed. Please see
498               **         any other messages printed along with this
499               **         message."
500               */
501               raiserror 18409, "setrepdbthreshold"
502               return (1)
503           end
504   
505           /* 
506           ** We have requested to reset the threshold to the default value.
507           */
508           if (@threshold_value = 0)
509           begin
510               select @current_threshold = getrepdbthreshold(@dbid)
511               /*
512               ** 19950, " The replication threshold for '%1!' has been reset 
513               **	    to '%2!'."
514               */
515               exec sp_getmessage 19950, @msg output
516               select @outvalue = convert(varchar(10), @current_threshold)
517               print @msg, @dbname, @outvalue
518               return (0)
519           end
520   
521           /*
522           ** 19932, 'The replication threshold for '%1!' is '%2!'."
523           */
524           exec sp_getmessage 19932, @msg output
525           print @msg, @dbname, @setmode
526           return (0)
527   
528   
529       end
530   


exec sp_procxmode 'sp_setrepdbmode', 'AnyMode'
go

Grant Execute on sp_setrepdbmode to public
go
DEFECTS
 MINU 4 Unique Index with nullable columns master..sysmessages master..sysmessages
 MTYP 4 Assignment type mismatch @sqlrep: varchar(4) = varchar(255) 108
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 146
 QTYP 4 Comparison type mismatch smallint = int 146
 TNOU 4 Table with no unique index master..spt_values master..spt_values
 MGTP 3 Grant to public master..spt_values  
 MGTP 3 Grant to public master..sysdatabases  
 MGTP 3 Grant to public master..sysmessages  
 MGTP 3 Grant to public sybsystemprocs..sp_setrepdbmode  
 MNER 3 No Error Check should check return value of exec 116
 MNER 3 No Error Check should check return value of exec 117
 MNER 3 No Error Check should check return value of exec 118
 MNER 3 No Error Check should check return value of exec 256
 MNER 3 No Error Check should check return value of exec 318
 MNER 3 No Error Check should check return value of exec 324
 MNER 3 No Error Check should check return value of exec 412
 MNER 3 No Error Check should check return value of exec 437
 MNER 3 No Error Check should check return value of exec 446
 MNER 3 No Error Check should check return value of exec 474
 MNER 3 No Error Check should check return value of exec 515
 MNER 3 No Error Check should check return value of exec 524
 MUCO 3 Useless Code Useless Brackets 88
 MUCO 3 Useless Code Useless Brackets 120
 MUCO 3 Useless Code Useless Brackets 127
 MUCO 3 Useless Code Useless Brackets 154
 MUCO 3 Useless Code Useless Brackets 161
 MUCO 3 Useless Code Useless Brackets 172
 MUCO 3 Useless Code Useless Brackets 179
 MUCO 3 Useless Code Useless Brackets 202
 MUCO 3 Useless Code Useless Brackets 204
 MUCO 3 Useless Code Useless Brackets 209
 MUCO 3 Useless Code Useless Brackets 214
 MUCO 3 Useless Code Useless Brackets 222
 MUCO 3 Useless Code Useless Brackets 225
 MUCO 3 Useless Code Useless Brackets 227
 MUCO 3 Useless Code Useless Brackets 233
 MUCO 3 Useless Code Useless Brackets 269
 MUCO 3 Useless Code Useless Brackets 277
 MUCO 3 Useless Code Useless Brackets 284
 MUCO 3 Useless Code Useless Brackets 287
 MUCO 3 Useless Code Useless Brackets 290
 MUCO 3 Useless Code Useless Brackets 292
 MUCO 3 Useless Code Useless Brackets 294
 MUCO 3 Useless Code Useless Brackets 296
 MUCO 3 Useless Code Useless Brackets 304
 MUCO 3 Useless Code Useless Brackets 312
 MUCO 3 Useless Code Useless Brackets 327
 MUCO 3 Useless Code Useless Brackets 332
 MUCO 3 Useless Code Useless Brackets 337
 MUCO 3 Useless Code Useless Brackets 342
 MUCO 3 Useless Code Useless Brackets 349
 MUCO 3 Useless Code Useless Brackets 352
 MUCO 3 Useless Code Useless Brackets 358
 MUCO 3 Useless Code Useless Brackets 365
 MUCO 3 Useless Code Useless Brackets 369
 MUCO 3 Useless Code Useless Brackets 379
 MUCO 3 Useless Code Useless Brackets 383
 MUCO 3 Useless Code Useless Brackets 387
 MUCO 3 Useless Code Useless Brackets 391
 MUCO 3 Useless Code Useless Brackets 395
 MUCO 3 Useless Code Useless Brackets 400
 MUCO 3 Useless Code Useless Brackets 406
 MUCO 3 Useless Code Useless Brackets 413
 MUCO 3 Useless Code Useless Brackets 417
 MUCO 3 Useless Code Useless Brackets 421
 MUCO 3 Useless Code Useless Brackets 428
 MUCO 3 Useless Code Useless Brackets 432
 MUCO 3 Useless Code Useless Brackets 449
 MUCO 3 Useless Code Useless Brackets 453
 MUCO 3 Useless Code Useless Brackets 455
 MUCO 3 Useless Code Useless Brackets 460
 MUCO 3 Useless Code Useless Brackets 468
 MUCO 3 Useless Code Useless Brackets 477
 MUCO 3 Useless Code Useless Brackets 482
 MUCO 3 Useless Code Useless Brackets 491
 MUCO 3 Useless Code Useless Brackets 494
 MUCO 3 Useless Code Useless Brackets 502
 MUCO 3 Useless Code Useless Brackets 508
 MUCO 3 Useless Code Useless Brackets 518
 MUCO 3 Useless Code Useless Brackets 526
 QAFM 3 Var Assignment from potentially many rows 108
 QISO 3 Set isolation level 92
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error, langid}
144
 VNRD 3 Variable is not read @rep_constant 97
 VNRD 3 Variable is not read @update 130
 VNRD 3 Variable is not read @delete 131
 VNRD 3 Variable is not read @inserts 132
 VNRD 3 Variable is not read @selinto 133
 VNRD 3 Variable is not read @sptlang 147
 VNRD 3 Variable is not read @dbuid 185
 VNRD 3 Variable is not read @gp_enabled 212
 VNRD 3 Variable is not read @dummy 232
 VUNU 3 Variable is not used @procval 33
 VUNU 3 Variable is not used @objid 37
 VUNU 3 Variable is not used @newstat 41
 MSUB 2 Subquery Marker 143
 MTR1 2 Metrics: Comments Ratio Comments: 43% 25
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 37 = 57dec - 22exi + 2 25
 MTR3 2 Metrics: Query Complexity Complexity: 251 25

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_sqlrep_check_syntax  
reads table master..sysmessages (1)  
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..sysconfigures (1)  
   reads table master..syscurconfigs (1)  
calls proc sybsystemprocs..sp_getmessage  
   reads table master..syslanguages (1)  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table master..sysmessages (1)  
   reads table sybsystemprocs..sysusermessages  
reads table master..sysdatabases (1)  
reads table master..spt_values (1)